Excel GROUPBY Function: Pivot-Style Summaries With a Formula

The GROUPBY function in Excel builds a pivot-style summary with one formula: it groups your rows by a column, such as Region, and totals another, such as Sales. The result spills into the cells below and updates the moment the data changes, with no Refresh button. This guide covers the syntax, the optional arguments for headers, totals, sorting and filtering, and how to get the same summary in versions of Excel that don't have GROUPBY.

What is the GROUPBY function?#

GROUPBY takes a column to group by, a column of values and a function, then returns one row per group with the result. It is a dynamic array function: you type it in one cell and the answer spills into as many rows as it needs. The guide to dynamic arrays in Excel explains spilling.

Version note: GROUPBY is available in Microsoft 365, including Excel for the web. It is not in Excel 2021 or Excel 2024, where it returns #NAME?. If you share a file with people on those versions, use the fallback later in this guide.

The examples use this sales table in A1:C8:

GROUPBY syntax#

GROUPBY has three required arguments and five optional ones:

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

Notice that the function is written as a name, SUM, with no brackets. GROUPBY applies it to each group for you.

How to use GROUPBY: a basic summary#

To total sales by region, type this in an empty cell such as E1:

=GROUPBY(A2:A8,C2:C8,SUM)

The result spills into two columns:

GROUPBY sorts the groups A to Z and adds a Total row by default. Swap SUM for AVERAGE, COUNT, MAX or MIN to change the summary. For example, =GROUPBY(A2:A8,C2:C8,COUNT) counts the sales in each region: 2 for East, 3 for North and 2 for South.

Show the headers#

To keep the column titles, include the header row in both ranges and set field_headers to 3 ("yes, and show them"):

=GROUPBY(A1:A8,C1:C8,SUM,3)

The spilled table now starts with Region and Sales, ready to paste into a report or feed a chart.

How to sort, filter and remove totals#

The optional arguments turn a basic summary into the exact table you need. Leave an argument empty with an extra comma to skip it.

Sort by total, largest first. The totals are the 2nd column of the result, so use -2:

=GROUPBY(A2:A8,C2:C8,SUM,,,-2)

The order becomes North (2,300), East (2,100), South (1,350), with the Total row still at the bottom.

Remove the total row. Set total_depth to 0:

=GROUPBY(A2:A8,C2:C8,SUM,,0)

Keep only some rows. Add a TRUE/FALSE test as the filter_array:

=GROUPBY(A2:A8,C2:C8,SUM,,,,B2:B8="Laptop")

This totals laptop sales only: East 1,500, North 2,000, South 900 and a total of 4,400. The filter works like the FILTER function's include argument.

Group by two fields. Give GROUPBY two adjacent columns as row_fields, such as A2:B8, to group by region and then product. Set total_depth to 2 if you want a subtotal for each region as well as the grand total.

How to get the same result without GROUPBY#

In Excel 2021, Excel 2024 or any version without GROUPBY, two formulas build the same summary. First, list each region once, sorted, in E2:

=SORT(UNIQUE(A2:A8))

This spills East, North and South. The UNIQUE function guide covers how it removes duplicates, and the UNIQUE lesson practises it. Then, in F2, total each one with SUMIFS:

=SUMIFS(C2:C8,A2:A8,E2#)

The E2# refers to the whole spilled list, so SUMIFS returns one total per region: 2,100, 2,300 and 1,350. Add a grand total underneath with SUM if you need one. The SUMIFS function guide explains the argument order.

In Excel 2019 and earlier, which have no UNIQUE, type the region names yourself and fill an ordinary SUMIFS down. The guide to summarising data without a PivotTable walks through all three approaches, and the pivot-style summary lesson lets you build one step by step.

GROUPBY vs a PivotTable#

Both produce the same kind of summary, but they suit different jobs:

A PivotTable is still the quickest way to explore a new data set: drag a field in, look, drag it out. GROUPBY is better for a report table that should always be current, or one that other formulas depend on. The guide to PivotTables for beginners covers the PivotTable side, and PivotTable vs SUMIFS compares the formula approach in more depth.

GROUPBY also has a sibling, PIVOTBY, which adds a column field to make a cross-tab, such as regions down the side and products across the top. It is also a Microsoft 365 function.

Common GROUPBY errors and fixes#

Most GROUPBY problems are about spilling, versions or range sizes:

1. #NAME? Your Excel version doesn't have GROUPBY. Use SORT, UNIQUE and SUMIFS instead.
2. #SPILL! Something is in the way of the result. Clear the cells below and to the right of the formula.
3. An error from mismatched ranges. The row_fields and values ranges must have the same number of rows. Make them start and end on the same rows.
4. Headers counted as a group. You included the header row but didn't set field_headers. Start both ranges at row 2, or set field_headers to 3.
5. The function has brackets. Write SUM, not SUM(), in the third argument.

Practise it#

GROUPBY is the newest way to do something every analyst does: total a column by group. Try the SUMIFS exercise on this page, then practise pivot-style summaries in a live spreadsheet and the modern Excel functions lesson, which covers the dynamic array functions behind this approach. Each lesson checks your formula instantly.