Excel GROUPBY Function — Group and Summarize Data With One Formula

TL;DR — GROUPBY(row_fields, values, function) takes a column to group by, a column of numbers, and an aggregation, and spills a finished summary table: =GROUPBY(C2:C1000, D2:D1000, SUM) returns each region with its total. The one rule that catches everyone: pass the function by name — SUM, not SUM() — because the third argument is a function, not a value. Unlike a pivot table, it recalculates the instant your data changes, no Refresh. It's the one-formula replacement for UNIQUE + SUMIFS. Needs Excel 365 (rolled out 2024–2025); not in 2021 or earlier.

=GROUPBY(C2:C1000, D2:D1000, SUM)                 ' each region, with its total sales
=GROUPBY(C2:C1000, D2:D1000, AVERAGE)             ' swap the aggregation — just the name
=GROUPBY(C2:C1000, D2:D1000, COUNT)               ' how many rows per region
=GROUPBY(B2:C1000, D2:D1000, SUM)                 ' two grouping columns -> a hierarchy
=GROUPBY(C2:C1000, D2:D1000, SUM, 0, 0, -2)       ' no totals, sorted by total, descending

For thirty years, summarizing data in Excel meant leaving your formulas behind. You either built a pivot table — a manual object that goes stale the moment the data changes and needs a right-click Refresh — or you hand-assembled a summary with UNIQUE to list the categories and SUMIFS to total each one. GROUPBY (2024) ends both chores. You stop building a summary and start describing one: name the field to group by, the values to aggregate, and how — and Excel spills the live report.

What you'll learn

The mental model: you describe a report, Excel spills it
The three required arguments — and the trap in the third one
Why you pass SUM, not SUM() (the #1 error)
Grouping by two or more columns, and totaling more than one value
Adding grand totals and subtotals with total_depth
Sorting by the aggregated value without wrapping in SORT
Why it beats UNIQUE + SUMIFS and, often, the pivot table

The mental model: describe the report, don't build it

A pivot table is a thing you construct — drag fields into boxes, and it produces a block of cells that is disconnected from your formulas and frozen until you refresh it. GROUPBY is a formula that produces the same block but keeps it alive. The shift is from noun to verb: instead of building a summary object, you write one sentence that says "group these, total those."

=GROUPBY(C2:C1000, D2:D1000, SUM)
'        └ group by      └ add up   └ how

Read it left to right: group by the region column, add up the sales column, using SUM. The result spills — one row per distinct region, each with its total, plus a grand total — and every one of those numbers updates the instant a value in D2:D1000 changes. No Refresh, no stale pivot cache. That live-recalculation is the whole reason to reach for GROUPBY instead of a pivot table when the summary feeds a dashboard.

The three required arguments

Everything else is optional. A working GROUPBY needs exactly three things:

=GROUPBY( row_fields , values , function )
'          what to      what to    how to
'          group by     aggregate  aggregate

row_fields — the column (or columns) whose distinct values become the rows of your summary: C2:C1000, the region column.
values — the column of data to crunch: D2:D1000, the sales.
function — how to crunch it: SUM, AVERAGE, COUNT, MAX, MIN, and more.

Give it those three and you get a sorted, de-duplicated, totaled table. But that third argument is where nearly everyone stumbles the first time.

The #1 trap: pass SUM, not SUM()

The function argument is not a value — it's a function itself, handed over by name so that GROUPBY can apply it to each group for you. You are passing the tool, not the result of using it.

=GROUPBY(C2:C1000, D2:D1000, SUM)        ' CORRECT — the name of the function
=GROUPBY(C2:C1000, D2:D1000, SUM())      ' WRONG — #CALC!/error, you called it with no data
=GROUPBY(C2:C1000, D2:D1000, SUM(D:D))   ' WRONG — that's one number, not a function

Think of SUM here the way you'd hand someone a recipe, not a finished cake. GROUPBY takes the SUM recipe and runs it once per region behind the scenes. Writing SUM() or SUM(D2:D1000) collapses everything to a single value before GROUPBY ever sees the groups, which is exactly what you don't want. The rule: the third argument is always a bare function name — SUM, AVERAGE, COUNT, PERCENTOF — never a called one.

Group by more than one column

Point row_fields at two adjacent columns and GROUPBY builds a hierarchy — the first column is the outer group, the second nests inside it:

=GROUPBY(B2:C1000, D2:D1000, SUM)   ' Region, then Product within each region, each subtotaled

You get Region as the top level and, indented beneath each, its Products — the same shape a pivot table gives you from two row fields, but as a live formula. The columns must be adjacent (or stacked with HSTACK); they read left to right as outermost to innermost group.

Total more than one value at once

Widen values to several columns and every one gets aggregated side by side:

=GROUPBY(C2:C1000, D2:E1000, SUM)   ' each region's total Sales AND total Units

Now each region row shows two totals — Sales and Units — in adjacent columns. This is the everyday reporting case (revenue, cost, units, margin, all by region) that used to mean one SUMIFS per metric per category; here it's one formula that grows as you widen the values range.

Add totals and subtotals with total_depth

The fifth argument, total_depth, controls the total rows. Omit it and Excel is automatic — a grand total, plus subtotals where the grouping allows. You can pin it down instead:

=GROUPBY(C2:C1000, D2:D1000, SUM, , 0)    ' 0 = no totals at all
=GROUPBY(C2:C1000, D2:D1000, SUM, , 1)    ' 1 = grand total only
=GROUPBY(B2:C1000, D2:D1000, SUM, , 2)    ' 2 = grand total + subtotal per outer group
=GROUPBY(C2:C1000, D2:D1000, SUM, , -1)   ' negative = totals at the TOP instead of the bottom

The two commas skip field_headers (the fourth argument) to reach total_depth. A negative value is the small, useful trick most people miss: it moves the totals above the detail (-1 grand only, -2 with subtotals), which is what you want when the summary sits at the top of a dashboard and you don't want the grand total scrolling off the bottom.

Sort by the aggregated value — no SORT needed

The instinct is to wrap the whole thing in SORT to rank groups by size. You don't have to — sort_order is built in, and it sorts by result columns, not just the group label:

=GROUPBY(C2:C1000, D2:D1000, SUM, , , 2)    ' sort ascending by column 2 (the total)
=GROUPBY(C2:C1000, D2:D1000, SUM, , , -2)   ' sort DESCENDING by the total — biggest region first

sort_order is a column number: 1 is the group column, 2 is the first aggregated column, and a negative number sorts that column descending. So -2 gives you the classic "biggest first" ranking in a single argument. Reaching for SORT around a GROUPBY almost always means you forgot this argument exists.

Why it replaces UNIQUE + SUMIFS

Before GROUPBY, a formula-based summary took two steps: list the distinct categories, then total each one.

' The old two-step pattern:
=UNIQUE(C2:C1000)                   ' step 1: get the list of regions (spills down)
=SUMIFS(D:D, C:C, F2#)              ' step 2: SUMIFS each region beside it

' The GROUPBY replacement:
=GROUPBY(C2:C1000, D2:D1000, SUM)   ' both steps, one formula, plus totals and sorting

The old pattern still works and is worth knowing — but GROUPBY folds the UNIQUE, the SUMIFS, the sorting, and the totals into one expression that can't drift out of sync (in the two-step version, a UNIQUE that spills further than your SUMIFS column silently drops categories). When you need the categories and the aggregates together, GROUPBY is the cleaner tool; keep SUMIFS for pulling a single conditional total into a specific cell.

The judgment call

Use GROUPBY when you want a summary that stays live and feeds other formulas — a dashboard tile, a figure that recomputes as data lands, a table you'll reference elsewhere. Use a pivot table when you want to explore interactively: drag fields around, add slicers, double-click to drill into the source rows. Keep SUMIFS for the one-off "put this region's total in this exact cell." The two mechanical rules that prevent almost every failure: the third argument is a bare function name (SUM, never SUM()), and totals, sorting, and filtering are arguments you already have — reach for total_depth, sort_order, and filter_array before you wrap the formula in anything. Once "summarize this" is a formula instead of an object, reporting stops being a chore you redo and becomes something that maintains itself.

How ExcelMaster helps

GROUPBY is powerful but its optional arguments are positional — miss a comma and you're setting the wrong one. Tell ExcelMaster "total sales by region, sorted biggest first, with a grand total" and it writes =GROUPBY(C2:C1000, D2:D1000, SUM, , 1, -2) with the skip commas in the right places. Ask for "average order value and order count per customer" and it widens values and picks the aggregations. And if you paste a GROUPBY that returns #CALC! because it says SUM() instead of SUM, or you're still maintaining a UNIQUE + SUMIFS block by hand, it spots the pattern and rewrites it as one formula.

Frequently asked questions

What does GROUPBY do in Excel?

GROUPBY(row_fields, values, function) groups rows by the distinct values in row_fields, aggregates the matching values with function (like SUM or AVERAGE), and spills a sorted, totaled summary table. It's a formula version of a pivot table's basic behavior, and it recalculates automatically whenever the underlying data changes.

Why do I pass SUM instead of SUM() in GROUPBY?

Because the third argument is a function, not a value. GROUPBY applies that function to each group itself, so you hand it the function by name — SUM — and let it do the calling. Writing SUM() or SUM(D:D) produces one number or an error instead of a per-group aggregation. The same applies to AVERAGE, COUNT, MAX, PERCENTOF, and any LAMBDA.

How do I sort a GROUPBY by the total, largest first?

Use the sort_order argument (the sixth): =GROUPBY(C2:C1000, D2:D1000, SUM, , , -2). The number is a column index — 2 is the first aggregated column — and a negative sign sorts it descending, so -2 ranks groups from biggest total to smallest. You don't need to wrap the formula in SORT.

Can GROUPBY group by two columns and total two values?

Yes. For two grouping levels, give row_fields two adjacent columns (=GROUPBY(B2:C1000, D2:D1000, SUM)) and the first nests the second. For two metrics, widen values to two columns (=GROUPBY(C2:C1000, D2:E1000, SUM)) and each is aggregated side by side. You can do both at once.

Which Excel versions have GROUPBY?

GROUPBY is a Microsoft 365 function that rolled out through 2024–2025, first to Insiders and then to the Current Channel. It is not available in Excel 2021, 2019, 2016, or Excel for the web on older builds. If =GROUPBY(...) returns #NAME?, your build doesn't have it yet — the UNIQUE + SUMIFS pattern is the fallback.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-18.