Summarize with GROUPBY

Excel Formulas › Dynamic Arrays

365 (2024+)GROUPBY

GROUPBY builds a grouped summary — like a one-formula PivotTable. Give it the grouping column, the values, and an aggregation, and it spills totals per group, sorted and ready.

Quick formula: total sales per region in one formula:
=GROUPBY(Region, Sales, SUM)
Returns each unique region with its summed sales — a live grouped table that refreshes with the data, no pivot required.

Functions used (tap for the full reference guide):

GROUPBYSUM

The example

Sales totaled by region.

AB
1RegionSales
2East42,000
3West38,500

The formula

Group, then aggregate:

=GROUPBY(Region, Sales, SUM) // unique regions + summed sales

How it works

GROUPBY does grouping + aggregation in one step:

  1. First argument: the row field to group by (e.g. Region).
  2. Second: the values to aggregate.
  3. Third: the function — SUM, AVERAGE, COUNT, or a custom LAMBDA.
  4. Optional arguments add totals, sort order, and even a second grouping level. The result spills and updates live.

A formula PivotTable. GROUPBY (and its cousin PIVOTBY) give pivot-style summaries that recalc automatically — no refresh, no GETPIVOTDATA. They’re a 2024 addition to Excel 365; where unavailable, SUMIFS over a UNIQUE list is the classic equivalent.

Try it: interactive demo

Live demo

Lines “region,sales” → totals by region.

Variations

Average instead

Swap the function:

=GROUPBY(Region, Sales, AVERAGE)

Add totals

Include a grand total row.

SUMIFS equivalent

Pre-2024:

=SUMIFS(Sales, Region, UNIQUE(Region))

Pitfalls & errors

365 (2024+) only. GROUPBY is brand new; many installs don’t have it yet — use SUMIFS+UNIQUE there.

Spill space. The grouped table spills; clear room or get #SPILL!.

Field arguments are columns. Pass single-column ranges for the group and values; mismatched sizes error.

Practice workbook

📊
Download the free Summarize with GROUPBY practice workbook
GROUPBY examples (formula text + result) with AVERAGE and SUMIFS-equivalent variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I summarize data by group with a formula in Excel?
Use =GROUPBY(groupColumn, values, SUM) to spill a grouped total table — like a live PivotTable. Requires Excel 365 (2024+).
What if I don't have GROUPBY?
Use SUMIFS over a UNIQUE list: =SUMIFS(values, group, UNIQUE(group)) gives the same per-group totals in older 365.
Can GROUPBY use a different aggregation?
Yes — pass SUM, AVERAGE, COUNT, MAX, or a custom LAMBDA as the third argument.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: PIVOTBY · Distinct count by group · Sum by month

Function references: GROUPBY · SUM