GROUPBY Function
Builds a grouped summary — like a mini PivotTable — in one formula.
Category
Lookup & Reference
Available in
Microsoft 365
Returns
A spilled summary table
Overview
GROUPBY groups your data by one field and aggregates another. =GROUPBY(B2:B100, C2:C100, SUM) gives a total for each unique value in column B.
It updates instantly with no refresh needed, and supports sorting, filtering and totals through optional arguments.
Syntax
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])
Arguments
| Argument | Required? | What it does |
|---|---|---|
| row_fields | Yes | The column(s) to group by. |
| values | Yes | The column(s) to aggregate. |
| function | Yes | How to aggregate: SUM, AVERAGE, COUNTA, MAX, PERCENTOF, or a LAMBDA. |
| field_headers | Optional | Whether your ranges include headers (0–3). |
| total_depth | Optional | 0 = no totals, 1 = grand total (default), 2 = subtotals too. Negative puts totals at the top. |
| sort_order | Optional | Column number to sort by; negative sorts descending. |
| filter_array | Optional | A TRUE/FALSE array to keep only certain rows. |
| field_relationship | Optional | 0 = hierarchy (default), 1 = table, for multiple row fields. |
Examples
Example 1 — Total sales by region
One formula replaces a PivotTable.
D2fx=GROUPBY(A2:A6, B2:B6, SUM)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Region | Sales | |
| 2 | East | 400 | East | 900 | |
| 3 | West | 350 | West | 625 | |
| 4 | East | 500 | Total | 1525 | |
| 5 | West | 275 |
=GROUPBY(A2:A6, B2:B6, SUM) Explain
Example 2 — Sorted largest first, no total
Sort by the second column descending and hide the total row.
=GROUPBY(A2:A6, B2:B6, SUM, , 0, -2) Explain
Use PERCENTOF as the function to show each group's share of the total.
Common mistakes
- Mismatched sizes. row_fields and values must have the same number of rows.
- Quoting the function. Pass SUM, not "SUM".
Notes
- Related functions: PIVOTBY, UNIQUE, SUMIFS, GETPIVOTDATA.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.