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

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

  1. Sales by region

    ABCDEF
    1RegionRepSalesNorth2400
    2NorthAna1200South800
    3SouthBen800West400
    4NorthChloe900Total3600
    5WestAna400
    6NorthAna300

    In E1: =GROUPBY(A2:A6, C2:C6, SUM) → North, 2400; South, 800; West, 400; Total, 3600

    Try this example

  2. Average sale by rep, no total

    ABCDEF
    1RegionRepSalesAna633.3333333
    2NorthAna1200Ben800
    3SouthBen800Chloe900
    4NorthChloe900
    5WestAna400
    6NorthAna300

    In E1: =GROUPBY(B2:B6, C2:C6, AVERAGE, 0, 0) → Ana, 633.3333333; Ben, 800; Chloe, 900

    Try this example

Common mistakes

Try GROUPBY in the playground

Practise it

Related functions