Excel GROUPBY: Filter by Multiple Criteria in Excel

Formulas & functions · Updated November 19, 2026

In Excel for Microsoft 365, pass a Boolean filter array to GROUPBY’s seventh argument to include only rows that meet your criteria. Multiply conditions for AND logic, or combine them with addition and test for a positive result for OR logic; for example, filter to East-region, shipped orders with =GROUPBY(B2:B100,D2:D100,SUM,,,,((A2:A100="East")*(C2:C100="Shipped"))=1).

What GROUPBY does—and what filtering means

GROUPBY summarizes data by one or more fields, returning a dynamic array with grouped labels and calculated values. For example, it can total sales by product, average hours by department, or count records by salesperson. Its filter_array argument lets you exclude source rows before the summary is calculated, without changing or deleting the original data.

This distinction matters: filtering rows is not the same as filtering the finished groups. In the example above, the criteria select East-region orders marked Shipped; GROUPBY then adds the sales for each product among those orders. It does not first total every region and then hide the unwanted results. If your aim is to keep only groups whose totals exceed a threshold, that is a separate post-summary step.

GROUPBY is available in supported Microsoft 365 versions, but it may not appear in older Excel editions or in every update channel at the same time. If Excel does not recognize the function, check that your Microsoft 365 installation is up to date or use a PivotTable or SUMIFS-based approach. FILTER is available in Microsoft 365 and Excel 2021 or later, but GROUPBY is not generally available in Excel 2021.

Set up the source data

Assume a worksheet has headers in row 1 and these columns: Region in A, Product in B, Status in C, and Sales in D. The formula uses rows 2 through 100, so the header row is not included in the criteria ranges or values. Keeping those ranges the same height is essential: each item in the filter array must correspond to the row at the same position in the grouping and values arrays.

In the example formula, the row field is Product, the values are Sales, and SUM is the aggregation. The three commas after SUM leave the optional field_headers, total_depth, and sort_order arguments blank, placing the filter array in argument seven. Excel’s formula bar displays the formula as a single expression; the result spills into cells below and to the right. Leave those cells clear so the output is not blocked by existing content.

Use consistent source ranges. If you extend the data beyond row 100, extend all four references together. A structured Excel Table is often easier to maintain because it expands as records are added, though formula references to table columns must be written using the table’s actual name and headers.

Combine criteria with AND

For AND logic, each condition must be true for a row to pass. Excel represents TRUE and FALSE comparisons as 1 and 0 in arithmetic expressions; multiplying the results produces 1 only when every condition is true. The final “=1” makes the filter array explicitly Boolean.

With Region in A2:A100, Product in B2:B100, Status in C2:C100, and Sales in D2:D100, this formula totals sales by product only for East-region orders that are Shipped:

=GROUPBY(B2:B100,D2:D100,SUM,,,,((A2:A100="East")*(C2:C100="Shipped"))=1)

If the qualifying East/Shipped records are Widget sales of 120 and 80, the Widget result is 200. Nonmatching rows do not contribute to any product total. The result includes other products only if they also have at least one row that satisfies both conditions.

You can add more AND conditions by multiplying another comparison into the expression. For example, to require a sales value of at least 100 as well, multiply by (D2:D100>=100). Be precise about the field being tested: filtering on a row’s sales amount is different from asking GROUPBY to show only products whose final total is at least 100.

Use OR logic and mixed conditions

For OR logic, add the TRUE/FALSE results and check whether the sum is greater than zero. A row passes if at least one listed condition is true. With Region in A2:A100 and Sales in D2:D100, this example groups sales by product for East or West rows:

=GROUPBY(B2:B100,D2:D100,SUM,,,,((A2:A100="East")+(A2:A100="West"))>0)

For mixed logic, use parentheses to show which conditions belong together. For instance, “East and Shipped, or West and Shipped” can be expressed as two AND pairs joined by OR. With the same column layout, use:

=GROUPBY(B2:B100,D2:D100,SUM,,,,(((A2:A100="East")*(C2:C100="Shipped"))+((A2:A100="West")*(C2:C100="Shipped")))>0)

Grouping conditions this way avoids a common logic error. The expression “East OR West AND Shipped” can be interpreted differently depending on how it is parenthesized, and the unqualified version may include East rows regardless of status. Write out the intended rule in words first, then put parentheses around each combined branch.

Filter first with FILTER, then group

You can also filter the grouping and value arrays first, then pass the reduced arrays to GROUPBY. This approach is useful when you want to inspect the filtered records separately or when you prefer to keep the filtering logic visible as a distinct operation. Both FILTER calls must use the same condition so that product labels remain aligned with their sales values.

With Region in A2:A100, Product in B2:B100, Status in C2:C100, and Sales in D2:D100, this formula groups the East/Shipped records:

=GROUPBY(FILTER(B2:B100,((A2:A100="East")*(C2:C100="Shipped"))=1),FILTER(D2:D100,((A2:A100="East")*(C2:C100="Shipped"))=1),SUM)

Unlike the direct filter_array method, this form nests FILTER inside GROUPBY and does not depend on placing criteria in GROUPBY’s seventh argument. It is longer, and with no matching rows FILTER returns a no-results error unless you provide its optional if_empty argument or handle that case with IFERROR. The GROUPBY filter_array method is usually simpler when the filtered records do not need to be displayed on their own.

Choose a summary function and interpret the result

GROUPBY can aggregate values with functions such as SUM, AVERAGE, COUNT, COUNTA, MAX, or MIN, depending on the data and the question. Use SUM for amounts, AVERAGE for a mean, and COUNT or COUNTA when you need a count; COUNT counts numeric values, while COUNTA counts nonempty values. Confirm that the chosen function matches the type of values being summarized.

GROUPBY returns a dynamic array, so its results update when the source data or criteria change. If you change a criterion from East to West, the summary recalculates. A spill error generally means Excel cannot place the full output because cells in the destination area contain data or merged cells. Clear the obstructing cells and verify that there is enough room for the groups and any headers or totals.

To filter groups by their calculated result—for example, show products with a total above 500—use a separate step on the GROUPBY output, such as FILTER, or use a PivotTable value filter. Do not put a group-total test into the source-row filter_array unless the test genuinely applies to each individual source row. A row-level threshold and a group-level threshold answer different questions.

Common problems and how to fix them

  • Excel shows #NAME?. Your Excel version may not support GROUPBY, or the function name may be misspelled. Check for Microsoft 365 updates and use a PivotTable or another summary method if GROUPBY is unavailable.
  • The totals seem too large or small. Check the filter logic, especially parentheses and AND/OR operators. Confirm that every range starts and ends on the same rows and that the intended status or category is spelled exactly as it appears in the data.
  • The result is empty or shows an error. There may be no rows that meet all conditions. Check for extra spaces, inconsistent labels, dates stored as text, or numbers stored as text. In the FILTER-first method, no matches require handling the empty-result case.
  • New records are missing. A fixed range ending at row 100 excludes records below that row. Expand all related ranges together or convert the source into an Excel Table and refer to its columns.
  • The formula will not spill. Clear cells in the intended result area. Dynamic-array formulas cannot spill over occupied cells, and merged cells can also prevent the output from expanding.

GROUPBY compared with PivotTables and SUMIFS

GROUPBY is a good choice when you want a formula-driven summary that updates automatically and can sit directly in a worksheet. Its filter_array argument keeps row filtering and aggregation together, and formula criteria are easy to edit. The trade-off is that it requires a compatible Excel version and that users must be comfortable reading a dynamic-array formula.

A PivotTable is often more approachable for interactive analysis. It can group and summarize fields through the PivotTable Fields pane, and its filter controls let a reader explore categories without editing a formula. PivotTables may need refreshing after the source changes, depending on the source and workbook setup. Use a PivotTable when users need interactive exploration or when GROUPBY is not available.

SUMIFS is a useful alternative when the desired output has a known layout, such as one total per listed product. It supports multiple criteria directly, but you must provide the product labels and write a separate formula for each summary row. Use GROUPBY when you want Excel to produce the groups dynamically; use SUMIFS when you need a fixed report format or a familiar formula compatible with older Excel versions.

Frequently Asked Questions

Can GROUPBY use more than two criteria?
Yes. Multiply additional comparisons for conditions that must all be true, or combine parenthesized groups with addition for OR logic. Keep each condition range aligned to the same source rows.

Does GROUPBY filter the source data permanently?
No. The filter_array argument only controls which rows contribute to the calculated result. It does not hide, delete, or change the original records.

Can I filter by a date range?
Yes. Use comparisons such as a date greater than or equal to a start date and less than an end date, combining them with AND logic. Make sure the source values are real Excel dates, not text that merely looks like dates.

Why does my GROUPBY formula return #NAME? in Excel 2021?
GROUPBY is a newer function for supported Microsoft 365 builds and is not generally included in Excel 2021. Use a PivotTable, SUMIFS, or another method supported by your edition.

Can I show only groups with totals above a limit?
Yes, but that is a condition on the summarized groups, not on individual source rows. Filter the GROUPBY result separately or apply a value filter in a PivotTable so the threshold is tested against each group’s total.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.