GROUPBY Function

Builds a grouped summary — like a mini PivotTable — in one formula.

Category

Lookup & Reference

Available in

Microsoft 365

Returns

A spilled summary table

Taught hands-on in Level 5: Expert Techniques & Workbook Architecture · Modern Functions module

Overview

GROUPBY groups your data by one field and aggregates another. =GROUPBY(B2:B100, C2:C100, SUM) gives a total for each unique value in column B.

It updates instantly with no refresh needed, and supports sorting, filtering and totals through optional arguments.

Syntax

=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])

Arguments

ArgumentRequired?What it does
row_fieldsYesThe column(s) to group by.
valuesYesThe column(s) to aggregate.
functionYesHow to aggregate: SUM, AVERAGE, COUNTA, MAX, PERCENTOF, or a LAMBDA.
field_headersOptionalWhether your ranges include headers (0–3).
total_depthOptional0 = no totals, 1 = grand total (default), 2 = subtotals too. Negative puts totals at the top.
sort_orderOptionalColumn number to sort by; negative sorts descending.
filter_arrayOptionalA TRUE/FALSE array to keep only certain rows.
field_relationshipOptional0 = hierarchy (default), 1 = table, for multiple row fields.

Examples

Example 1 — Total sales by region

One formula replaces a PivotTable.

D2fx=GROUPBY(A2:A6, B2:B6, SUM)
ABCDE
1RegionSalesRegionSales
2East400East900
3West350West625
4East500Total1525
5West275
=GROUPBY(A2:A6, B2:B6, SUM) Explain

Example 2 — Sorted largest first, no total

Sort by the second column descending and hide the total row.

=GROUPBY(A2:A6, B2:B6, SUM, , 0, -2) Explain
Use PERCENTOF as the function to show each group's share of the total.

Common mistakes

  • Mismatched sizes. row_fields and values must have the same number of rows.
  • Quoting the function. Pass SUM, not "SUM".

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.