Dynamic arrays
GROUPBY
Groups and aggregates with one formula, like a PivotTable. Pass the function without parentheses: SUM.
Syntax
GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])Example: =GROUPBY(A2:A20,C2:C20,SUM)
Available in: Microsoft 365 only
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =GROUPBY(A2:A7,C2:C7,SUM)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Units | Price | Result | ||
| 2 | East | Pen | 120 | 2.5 | East | 180 | |
| 3 | West | Pen | 80 | 2.5 | North | 60 | |
| 4 | East | Book | 45 | 12 | West | 110 | |
| 5 | North | Book | 60 | 12 | Total | 350 | |
| 6 | West | Lamp | 30 | 25 | |||
| 7 | East | Lamp | 15 | 25 |
Result in F2: East