Excel GROUPBY and PIVOTBY for Totals
By Szabó Gergő · Updated
GROUPBY is a formula pivot with one row field. PIVOTBY adds a column field. Both spill and update when the source changes.
Syntax and arguments
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])- row_fields
- The category column to group.
- values
- The numbers to aggregate.
- function
- SUM, AVERAGE, COUNTA, or a LAMBDA.
GROUPBY / PIVOTBY examples
Region B, amount D.
=GROUPBY(B2:B200,D2:D200,SUM)One row per region with a total.
Status in C.
=GROUPBY(C2:C200,C2:C200,COUNTA)COUNTA on the same column is a row count.
PIVOTBY available.
=PIVOTBY(B2:B200,TEXT(A2:A200,"yyyy-mm"),D2:D200,SUM)Regions on rows, months on columns.
Common mistakes
Including headers when field_headers is wrong
Set field_headers to 1 when the first row is a label, or start ranges at the data row.
Using it in Excel 2021
GROUPBY and PIVOTBY need a current Microsoft 365 build.
Expecting slicers
These are formulas. Use FILTER on the source or the filter_array argument.
GROUPBY / PIVOTBY FAQ
GROUPBY vs a PivotTable?
PivotTables have slicers and a UI. GROUPBY is live, file-light, and easy to reference.
Can I sort the totals?
Yes. sort_order can be 1 or -1 on the values. See current Microsoft docs for the argument position.
Does Sheets have this?
Use QUERY group by, or a Pivot Table. There is no GROUPBY worksheet function in Sheets.