To group Excel data while applying multiple row-level criteria, use the filter_array argument in GROUPBY and combine AND tests with multiplication. For example, =GROUPBY(A2:A100,C2:C100,SUM,1,0,,(B2:B100="West")*(D2:D100="Open")) groups the values in column A and sums column C only for rows where column B is West and column D is Open.
What GROUPBY does—and what “filter” means
GROUPBY is a dynamic-array function in Microsoft 365 that summarizes one or more fields by groups, much like a compact PivotTable. It can return summaries such as sums, counts, and averages without first building a separate report table. Its filter_array argument lets you include or exclude source rows as part of that calculation.
This filter is applied to records before they are grouped. In the example above, Excel first keeps rows meeting both criteria, then calculates a sum for each category in column A. It does not filter the completed group results. If you want to show only groups whose total exceeds a threshold, that is a separate, post-summary step.
The function is available in supported Microsoft 365 versions, but availability can depend on your update channel and rollout. If Excel shows #NAME? for GROUPBY, check that you are signed in to a Microsoft 365 version with the function, and update Excel if appropriate. Older perpetual versions may not include it.
Set up the source data
Assume a worksheet has these columns: category in A, region in B, sales in C, and status in D. Row 1 contains column headings, and rows 2 through 100 contain records. For example, if two West-region Open records are Hardware sales of 100 and 150, and an eligible Software record is 200, the grouped result should show Hardware with 250 and Software with 200.
Keep the source ranges aligned: the category, amount, and criteria ranges must cover the same rows. The formula uses rows 2 through 100 in every range, so each position in the filter array refers to the corresponding record in the other arrays. Including a heading in only one range, or using different end rows, can produce incorrect results or a size-mismatch error.
For a growing dataset, an Excel Table can make formulas easier to maintain. Select the data and use Insert > Table, then give the table clear column names. Structured references can expand as records are added, although formula details and function availability should be checked in the Excel version being used.
Use AND criteria with multiplication
In Excel array formulas, comparisons such as B2:B100="West" produce an array of TRUE and FALSE values. Multiplying two such arrays works as AND: TRUE is treated as 1 and FALSE as 0, so a row is included only when every multiplied test is true.
With categories in A2:A100, regions in B2:B100, sales in C2:C100, and statuses in D2:D100, this formula groups qualifying sales by category. It specifies that the supplied ranges contain data but no heading row, and sets total depth to zero so the result has no grand-total row:
=GROUPBY(A2:A100,C2:C100,SUM,1,0,,(B2:B100="West")*(D2:D100="Open"))
Here, the two criteria are West region and Open status. Add another test by multiplying another comparison, for example *(C2:C100>=100) to require sales of at least 100. Parentheses make the separate tests and their combination easier to read. The result spills into neighboring cells, so keep the output area clear.
Combine OR and AND conditions
Use addition for alternatives, then test whether their sum is greater than zero. For instance, the following includes Open records from either the West or East region. The region comparisons are alternatives; the status test must also be true.
Assuming the same layout, use:
=GROUPBY(A2:A100,C2:C100,SUM,1,0,,(((B2:B100="West")+(B2:B100="East"))>0)*(D2:D100="Open"))
The >0 test converts the addition of the region matches into a clear TRUE/FALSE condition. This is especially useful when criteria might overlap: adding two TRUE results can produce 2, but checking whether the sum is positive still treats the row as one qualifying record. For a more complex rule, such as West-and-Open or East-and-Pending, build each AND group separately and add the groups inside an outer greater-than-zero test.
Use FILTER before GROUPBY instead
You can also filter the category and value arrays first, then pass the resulting arrays to GROUPBY. This makes the selection logic visible as a separate step and can be helpful when you want to inspect or reuse the filtered records. The same keep array must be applied to both the category and sales ranges so the values remain paired.
With the same A:D layout, this formula uses LET to name the criteria array keep, then filters both source fields before summing by category:
=LET(keep,(B2:B100="West")*(D2:D100="Open"),GROUPBY(FILTER(A2:A100,keep),FILTER(C2:C100,keep),SUM,1,0))
FILTER and LET are available in Microsoft 365 and Excel 2021 or later. If there are no matching rows, FILTER normally returns #CALC! unless an if_empty value is supplied. Be cautious about supplying a placeholder in only one filtered array: a single fake category or amount can create a misleading group. For a report that needs a friendly no-results message, test whether any rows qualify before calling GROUPBY.
Choose between GROUPBY and FILTER
For straightforward criteria, putting the Boolean array in GROUPBY‘s filter argument is concise and keeps the summary in one function. Filtering first is useful when you also need the qualifying detail rows, want to make each stage easier to troubleshoot, or plan to use the filtered arrays for another calculation.
- Use
GROUPBY‘s filter argument when you need a grouped summary and the criteria are easy to read in one expression. - Use
FILTERseparately when the filtered records are useful on their own or need further processing. - Use a PivotTable when you want interactive field dragging, slicers, or a familiar report that colleagues can adjust manually.
- Use
SUMIFSwhen you need a fixed summary cell for a known set of criteria rather than a spilled list of groups.
SUMIFS can handle several AND conditions, but it does not by itself create a distinct list of categories. It is often paired with a category list or other functions. A PivotTable can also filter before summarizing, but its workflow differs from a formula that recalculates as source cells change.
Handle dates, text, and changing criteria
Date criteria should use actual Excel dates, not text that merely looks like a date. For example, a date window can be expressed as a lower-bound comparison multiplied by an upper-bound comparison. If you enter dates directly into a formula, use a date-construction function or refer to cells containing valid dates; avoid relying on ambiguous typed date text that can be interpreted differently by regional settings.
For changing selections, place the desired region or status in a cell and refer to it in the comparison. With the same data layout and the selected region in F1 and status in G1, the formula is:
=GROUPBY(A2:A100,C2:C100,SUM,1,0,,(B2:B100=F1)*(D2:D100=G1))
Now changing F1 or G1 recalculates the summary. Excel text comparisons in these formulas are generally not case-sensitive, so West and WEST normally match the same text. If capitalization is meaningful, use a case-sensitive test such as EXACT inside the array logic. Also decide how blanks should behave: a comparison to an empty criteria cell may select blank source cells, which may not be what a report is intended to show.
Common errors and ways to troubleshoot
If the formula returns an error or an unexpected summary, check the criteria array before changing the grouping logic. A temporary helper column can show which records qualify, or you can test a simple condition by itself and then add the other tests one at a time.
#VALUE!or a size mismatch: Confirm every input range starts and ends on the same rows. The filter array must have one result per source record.#SPILL!: Clear cells in the output spill area and check for merged cells that block the result.- Unexpectedly missing rows: Check spelling, trailing spaces, data types, and whether a blank criterion is matching blank records.
- Unexpected totals: Confirm the values column contains numbers, and verify that each source record appears only once if duplicates are not intended.
#NAME?: Check spelling and whether your Excel version includesGROUPBY. A function name unsupported by that version will not be repaired by changing the criteria.
A common misconception is that GROUPBY criteria need to be written as text such as “West AND Open.” They do not: each comparison creates an array, and the arithmetic operators combine those arrays row by row. Another is that the filter argument can select groups based on their calculated totals. It selects source rows; filtering groups by total requires filtering the resulting summary after it has been calculated.
Frequently Asked Questions
Can Excel GROUPBY use more than two criteria?
Yes. Multiply additional comparison arrays for additional AND conditions. For OR logic, add alternatives and test whether their sum is greater than zero. Keep every criteria range the same length as the fields being grouped.
Can I filter by multiple values in one column?
Yes. Add comparisons for the alternatives, such as region equals West or East, and use >0 to make the combined result a Boolean test. Multiply that test by other conditions that must also be true.
Does GROUPBY filter before or after it summarizes?
The filter_array argument selects source records before grouping and aggregation. To filter based on a group’s finished sum or count, apply a separate filter to the spilled summary.
Why does my GROUPBY formula return #CALC!?
One common cause is that no records meet the criteria, especially when using FILTER without its optional empty-result argument. Check the criteria and source values, and decide how the workbook should display a no-match result.
Can I do this in older Excel versions?
If your version lacks GROUPBY, use a PivotTable or a helper list of categories with formulas such as SUMIFS. The exact alternative depends on whether you need a dynamic spilled report or a manually refreshed summary.
Related
- Excel Dependent Drop-Down Lists in Microsoft 365
- Excel Dependent Drop-Down Lists With Spaces: Setup Guide
- Excel Dependent Drop-Down List: 3 Levels
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Named Range Not Appearing in Formula? FixesIf an Excel named range is not appearing as you type a formula, first confirm that the name…
- Excel RIGHT Function Examples: Extract Text from the EndUse Excel’s RIGHT function to return characters from the end of a text value. For example, if A2…
- SUMIF Excel Examples With Two Criteria to Match RowsTo total values that match two conditions in Excel, use SUMIFS rather than SUMIF. With regions in A2:A8,…
- Excel CONCATENATE: Does It Keep Text Formatting?No. Excel’s CONCATENATE, CONCAT, and TEXTJOIN functions combine values into text, but they do not preserve rich-text styling…
- DATEDIF in Excel: Calculate Days Between Dates CorrectlyTo get the number of days between a start date in A2 and an end date in B2,…
- Excel Filter Duplicates: Is Matching Case-Sensitive?No. In Excel for Microsoft 365 on Windows, the regular filter and built-in duplicate tools do not distinguish…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.