Excel functions / Dynamic arrays
GROUPBY function
Groups rows by one or more fields and aggregates values for each group, with a total row: a pivot by formula that updates live. Spills.
Syntax
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])
Arguments
row_fields: What to group by: one or more columns.values: The values to aggregate: one or more columns, the same height.function: How to aggregate, by name: SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, MEDIAN or PERCENTOF.field_headers(optional): 0 no headers, 1 headers not shown, 2 generate headers, 3 headers shown.total_depth(optional): 0 no total, 1 total at the bottom (default), -1 total at the top.sort_order(optional): The output column to sort by, from 1; negative sorts descending. Default 1.filter_array(optional): TRUE/FALSE for each row: only TRUE rows are used.
When to use it
GROUPBY summarises a table by the values of one column, like a small PivotTable written as a formula: totals by region, counts by product. It spills a row for each group and a grand total, and updates as the data changes.
Examples
Sales by region
A B C D E F 1 Region Rep Sales North 2400 2 North Ana 1200 South 800 3 South Ben 800 West 400 4 North Chloe 900 Total 3600 5 West Ana 400 6 North Ana 300 In E1:
=GROUPBY(A2:A6, C2:C6, SUM)→ North, 2400; South, 800; West, 400; Total, 3600Average sale by rep, no total
A B C D E F 1 Region Rep Sales Ana 633.3333333 2 North Ana 1200 Ben 800 3 South Ben 800 Chloe 900 4 North Chloe 900 5 West Ana 400 6 North Ana 300 In E1:
=GROUPBY(B2:B6, C2:C6, AVERAGE, 0, 0)→ Ana, 633.3333333; Ben, 800; Chloe, 900
Common mistakes
- The function in quotes. The function is given by name without quotes: SUM, not "SUM".
- Older versions of Excel. GROUPBY is in Excel for Microsoft 365 only. Elsewhere, use a PivotTable or SUMIFS with UNIQUE.
Practise it
Related functions
- PIVOTBY: Summarises values by row fields down the side and a column field across the top, with totals: a PivotTable by formula that updates live. Spills.
- SUMIFS: Adds the cells that meet every one of several conditions.
- UNIQUE: Removes duplicates from a list. The result spills.
- PERCENTOF: What share one set of values is of the whole: SUM(data_subset) ÷ SUM(data_all). Also works as the function in GROUPBY and PIVOTBY.