GROUPBY Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Summarize Excel rows with GROUPBY. Use explicit headers and totals, sort aggregates, filter paid records and understand hierarchy versus table relationships with worked results.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: GROUPBY groups rows by one or more fields and aggregates their associated values. It returns a formula result that can spill into several cells. Supply SUM as the aggregation function itself, rather than the quoted text “SUM” or a precomputed total.
Syntax and argument contract
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])
The first three arguments are required. Row fields are column-oriented grouping data; values are aligned column-oriented data to aggregate; function is an aggregation lambda or a function such as SUM, AVERAGE or COUNT. Multiple value columns or a vector of aggregation functions can return multiple aggregates.
Optional controls are:
- field_headers: omitted means automatic; 0 means no input headers; 1 means input headers present but hidden in output; 2 means no input headers but generate output headers; 3 means input headers present and shown. Auto detection examines whether the first values entry is text and the second is numeric.
- total_depth: omitted is automatic; 0 disables totals; 1 adds grand totals; 2 adds grand totals and subtotals; −1/−2 put those totals at the top. Subtotals need at least two grouping columns; deeper levels need enough fields.
- sort_order: a signed column index across row_fields followed by values; negative means descending. A vector is allowed for sorting only row fields. No explicit omitted-sort guarantee is asserted here.
- filter_array: a column of Boolean inclusion values with the same row count as row_fields.
- field_relationship: 0 or omitted is hierarchy; 1 is table. Table mode sorts field columns independently and does not support hierarchy-dependent subtotals.
Worked example: sales by region
Use this fixed source table in A1:D7. Sales values are numeric.
| Row | A · Region | B · Quarter | C · Sales | D · Status |
|---|---|---|---|---|
| 1 | Region | Quarter | Sales | Status |
| 2 | East | Q1 | 100 | Paid |
| 3 | East | Q2 | 150 | Paid |
| 4 | West | Q1 | 80 | Paid |
| 5 | West | Q2 | 120 | Pending |
| 6 | East | Q1 | 50 | Pending |
| 7 | West | Q2 | 30 | Paid |
- Leave F2:G3 empty.
- In F2 enter
=GROUPBY(A2:A7,C2:C7,SUM,0,0,1). - Expected rows are East | 300 and West | 230.
Header mode 0 is correct because the formula excludes row 1. Total depth 0 suppresses a separate grand-total row; sort index 1 requests region order. Independent sums are East 100+150+50 = 300 and West 80+120+30 = 230.
Second example: sort by the aggregate
=GROUPBY(A2:A7,C2:C7,SUM,0,0,-2) sorts descending on the second output field, the sales aggregate. Expected order remains East 300, then West 230. With sort index 2 instead, ascending aggregate order is West 230, then East 300.
This distinguishes the grouping key from its total. Do not assume a negative index means descending region names irrespective of which index was selected.
Advanced application: include only paid rows
=GROUPBY(A2:A7,C2:C7,SUM,0,0,1,D2:D7="Paid")
Expected rows: East | 250, West | 110. The Boolean filter includes East’s 100 and 150 and West’s 80 and 30. The total is 360, excluding pending 50 and 120 from the original 530.
For a two-level summary, use =GROUPBY(A2:B7,C2:C7,SUM,0,0,1). The expected grouped records are East/Q1 150, East/Q2 150, West/Q1 80, and West/Q2 150. No subtotal rows were requested. This is still grouping down one output axis, rather than making quarters into separate columns.
Troubleshooting and limitations
Keep input arrays aligned and Boolean filters the same height. Explicit header mode avoids accidentally treating the first numeric observation as a header. A blocked output range can cause #SPILL!; keep the output separate from the source.
A #NAME? or unrecognized function is a build-availability question, not proof that the source data is wrong. The individual reference does not enumerate every invalid-argument error, so no unsupported error-code table is invented here. Aggregation can hide bad source data; independently reconcile the full 530 and filtered 360 before trusting a report.
Compatibility and verification
Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365. Microsoft’s alphabetical catalog, checked October 17, 2026, identifies GROUPBY with a Microsoft 365 introduction marker. That marker does not establish an exact rollout date, update channel or perpetual-license release. The last functioning version is unverified; no retirement is asserted.
Microsoft’s GROUPBY/PIVOTBY announcement, retrieved October 17, 2026, records general availability in the Current Channel on September 25, 2024. This channel milestone does not establish support in every perpetual release or the first preview build. Test recognition in the intended Microsoft 365 build; do not use a broad Applies To list alone as proof of older-version support.
Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.
Related guides and source
SUM in Excel | SUMIFS in Excel | FILTER in Excel | LAMBDA in Excel
- Microsoft GROUPBY reference, retrieved October 17, 2026.