Excel like a finance pro.
Dynamic array function
GROUPBY
Summarizes data by groups in one formula, like a pivot table that recalculates live.
When to use it
A pivot table as a formula: group rows by one or more columns and aggregate another. Refreshes automatically, unlike a pivot.
The shape of it
- Syntax
=GROUPBY(row_fields, values, function, [field_headers], ...)
Worked examples
Sum by group
=GROUPBY(C2:C6,B2:B6,SUM) → West 25; East 80; North 40; Total 145
Total price by region, with a grand total row by default.
Average, no totals
=GROUPBY(C2:C6,B2:B6,AVERAGE,,0) → West 12.5; East 40; North 40
Total depth 0 drops the total row.
Two aggregates
=GROUPBY(C2:C6,B2:B6,HSTACK(SUM,COUNT)) → region, total, count
Two aggregations side by side.
Worth knowing
- Pass a LAMBDA as the function for custom aggregations.
- Sort with the sort_order argument rather than wrapping in SORT.
- PERCENTOF as the function gives share of total.
Where it goes wrong
- #NAME? before Excel 2024 / Microsoft 365.
- Row and value ranges must be the same height.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.