GROUPBY function
Helps a user group, aggregate, sort, and filter data based on the fields you specify.
Lookup and reference · Microsoft 365 · Guided Excel example
Where can you use it?
Reporting and operations teams. Use functions in this group to retrieve a field from an item, employee or order list, select the right record, or move values into another report.
Scenario in the workbook
Summarize amounts by region. Three source records become two groups. Leave the spill area empty.
A worked example from the workbook
| Row | A | B |
|---|---|---|
| 6 | Input | Second input |
| 7 | North | 12 |
| 8 | South | 8 |
| 9 | North | 5 |
Record 1
=GROUPBY(A7:A9,B7:B9,SUM)Illustrative outputNorth 17; South 8
See the inputs and instructions for all 3 examplesWorked examples
Summarize amounts by region. Three source records become two groups. Leave the spill area empty.
Column F provides expressions as text. Satisfy the stated requirements, then paste into an empty Excel cell and compare with column G. The result was not verified in desktop Excel here.
| Row | A | B |
|---|---|---|
| 6 | Input | Second input |
| 7 | North | 12 |
| 8 | South | 8 |
| 9 | North | 5 |
| Trial | Expression | Sample output |
|---|---|---|
| Record 1 | =GROUPBY(A7:A9,B7:B9,SUM) |
North 17; South 8 |
| Record 2 | =GROUPBY(A7:A9,B7:B9,SUM) |
Two groups |
| Record 3 | =GROUPBY(A7:A9,B7:B9,SUM) |
Grand total 25 |