GROUPBY in Excel: a summary table with one formula
GROUPBY totals a table by category in one formula that updates by itself. What it does, how it compares with SUMIFS and a PivotTable, and who has it.
GROUPBY builds a summary table from one formula.
=GROUPBY(A3:A11,C3:C11,SUM) lists each group once, adds up its
numbers, and puts a total under them. When the data changes, the table changes too.
GROUPBY and PIVOTBY are in Microsoft 365 only. Excel 2021 and Excel 2024 do not have them. In
those versions the formula shows #NAME?.
Here is an export of expense claims from a finance system. Each row is one claim. The manager wants the total for each team.
| Row | A | B | C |
|---|---|---|---|
| 1 | Claims | ||
| 2 | Team | Type | Amount |
| 3 | Finance | Hotel | 180 |
| 4 | Sales | Travel | 45 |
| 5 | Support | Travel | 60 |
| 6 | Sales | Hotel | 240 |
| 7 | Finance | Travel | 35 |
| 8 | Sales | Travel | 90 |
| 9 | Support | Hotel | 110 |
| 10 | Sales | Hotel | 120 |
| 11 | Finance | Travel | 20 |
The formula
In E3, type =GROUPBY(A3:A11,C3:C11,SUM). It fills E3 to F6:
| Finance | 235 |
| Sales | 495 |
| Support | 170 |
| Total | 900 |
You did not type the team names. GROUPBY found the 3 teams by itself. It also worked out that the table needs 4 rows.
The formula has 3 parts:
- A3:A11 is what to group by. Each team gets one row.
- C3:C11 is the numbers to add up.
- SUM is how to add them. You type the name only, with no brackets and no quote marks.
Change the last part and the same table answers a new question. With COUNT, it counts the claims for each team:
=GROUPBY(A3:A11,C3:C11,COUNT) gives Finance 3, Sales 4 and
Support 2, with 9 in the Total row.
AVERAGE, MAX and MIN work the same way.
To leave out the Total row, add a 0 as the fifth part:
=GROUPBY(A3:A11,C3:C11,SUM,,0). The two commas skip the fourth
part. GROUPBY has more optional parts, for sorting and filtering, but you can start without them.
Rows and columns: PIVOTBY
PIVOTBY groups down the side and across the top at the same time. Here it is by team and by type of claim.
=PIVOTBY(A3:A11,B3:B11,C3:C11,SUM) fills E3 to H7:
| Hotel | Travel | Total | |
| Finance | 180 | 55 | 235 |
| Sales | 360 | 135 | 495 |
| Support | 110 | 60 | 170 |
| Total | 650 | 250 | 900 |
The first range runs down the side. The second runs across the top. The third is the numbers. Totals appear on both edges, and the corner is 900, the total of everything.
The same summary with SUMIFS
This is how most people build the table today. Type the 3 team names in J3 to J5. In K3, type
=SUMIFS($C$3:$C$11,$A$3:$A$11,J3) and fill it down to K5.
You get the same 235, 495 and 170. For two conditions, add a second pair.
=SUMIFS(C3:C11,A3:A11,"Sales",B3:B11,"Hotel") gives 360, the
same as the PIVOTBY cell.
SUMIFS works in every version of Excel since 2007. The cost is the list of names. When a new team appears in the data, nobody adds it to column J. The summary is then short, and nothing tells you.
The fix is a check cell. =SUM(C3:C11) gives 900. If your
summary adds up to less, a team is missing. The
SUMIF and SUMIFS exercises cover the
function in more detail.
And a PivotTable
A PivotTable is the built-in tool. Select the data, then click Insert, then PivotTable. Drag Team to Rows and Amount to Values. You get the same table without typing a formula.
It is the best tool for a large export you do not know yet. You can drag fields in and out and try a new layout in seconds.
The catch is that it does not update by itself. Change a claim and the PivotTable still shows the old total. You have to click Refresh All on the Data tab. People forget, and send a report with last week's numbers.
Which one to use
| GROUPBY | SUMIFS | PivotTable | |
|---|---|---|---|
| Updates when data changes | Yes | Yes | After Refresh |
| Finds new groups | Yes | No | After Refresh |
| Works in Excel 2021 or older | No | Yes | Yes |
Use GROUPBY if everyone who opens the file has Microsoft 365. It updates and finds new groups, and nobody has to remember to refresh it.
Use SUMIFS if the file goes to someone with Excel 2021 or older. Also use it when a report needs rows in a fixed order that other cells point at.
Use a PivotTable to look through a big export first. Then decide what the report should show.
What goes wrong with GROUPBY
Something is in the way. The table needs empty cells below and to the right.
If one is not empty, you get #SPILL!. Leave space for the teams
that do not exist yet.
The data grows past the range. A claim typed in row 12 is outside A3:A11, so GROUPBY never sees it. Make the data an Excel table with Ctrl+T. Then use the table's columns in the formula, and the range grows with the data.
A formula points at one cell of the result. Say G3 reads F4, the Sales total. A new team adds a row to the result, and the rows can move. G3 may then read a different team. Read a total by name with SUMIFS instead.
GROUPBY is one of the functions that spill into many cells. The FILTER function article shows how spilling works in more detail.