What GROUPBY does
GROUPBY collects rows with matching labels and applies a calculation to each group. For example, several sales for Binder become one Binder row containing their total. The result recalculates as a worksheet formula.
Use PIVOTBY when you also want a second field, such as Region, spread across columns.
Practical examples
Total sales for each product
Create an Excel Table named Sales in A4:C10 with the following data. Keep the Table's Total Row off, and enter the sales amounts as numbers.
| Product | Region | Sales |
|---|---|---|
| Binder | North | 60 |
| Binder | South | 40 |
| Binder | North | 25 |
| Notebook | North | 30 |
| Notebook | South | 20 |
| Notebook | South | 15 |
Keep E4:F7 clear and enter this in E4:
=GROUPBY(Sales[[#All],[Product]],Sales[[#All],[Sales]],LAMBDA(x,SUM(x)),3,1,1)FormulaThe expected result is:
Product Sales
Binder 125
Notebook 65
Total 190ExampleLAMBDA(x,SUM(x)) adds the amounts passed to it for each group. Binder contributes 60 + 40 + 25 = 125; Notebook contributes 30 + 20 + 15 = 65.
The #All references include headers, and 3 displays them in the result. The next 1 requests the grand total; the last 1 sorts by the first grouping column. Keep the Table Total Row off so these references don't also include a source total.
Show product totals without a grand-total row
Using the same six-row Sales Table, replace the formula in E4 with:
=GROUPBY(Sales[[#All],[Product]],Sales[[#All],[Sales]],LAMBDA(x,SUM(x)),3,0,1)FormulaThe expected result is:
Product Sales
Binder 125
Notebook 65ExampleOnly total_depth changes, from 1 to 0. The two product sums are unchanged; the grand-total row is omitted. The sales summary walkthrough shows how Table references include a newly appended sale.
Common mistakes and notes
Match the header setting to the source
Sales[Product] supplies data rows, while Sales[[#All],[Product]] includes the header and any enabled Total Row. The examples deliberately include headers and use field_headers equal to 3. Don't copy that setting unchanged into a formula whose input has no headers.
A subtotal needs more than one grouping level
The examples have only Product as a grouping field. To use subtotals, supply at least two grouping columns and retain the hierarchical field relationship. The table relationship setting, 1, doesn't support subtotals.
A valid summary can still omit an amount
SUM ignores text and empty cells in its range input. Check that imported sales are numeric before trusting the totals. Align the grouping, value, and optional filter arrays to the same source rows.
Check function availability and leave room for the result
Microsoft's Applies To list names Excel for Microsoft 365. The explicit LAMBDA aggregation used here worked in the tested Excel for Microsoft 365 for Mac 16.114 installation. Microsoft also documents bare SUM as an aggregation argument, but that form returned #NAME? on that installation. This is a build-specific observation, not a claim that bare SUM fails everywhere.
Put the formula outside the source Table and keep enough neighboring cells clear for its output to grow. A new product needs another result row.