📎 This article includes 1 downloadable practice file ↓
In this article
GROUPBY groups rows by one or more columns and aggregates values — a live summary table that updates as data changes, without refreshing a pivot.
Syntax
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])| Argument | What it means |
|---|---|
row_fields |
Column(s) to group by. |
values |
Column(s) to aggregate. |
function |
SUM, AVERAGE, COUNTA, MAX… or a LAMBDA. |
Examples
Example 1
=GROUPBY(B2:B500, E2:E500, SUM)Total sales per region with a grand total.
Example 2
=GROUPBY(B2:B500, E2:E500, SUM, , , -2)Sorted by total, largest first.
GROUPBY in practice
Where you will use it
- Sales by region in one formula
- Summaries that update automatically
- Replace simple PivotTables
Worked example
Data: North 12000, South 8000, North 15000, West 22000, South 9000. Each row uses the function named in the first column.
| Aggregation | Formula | Result |
|---|---|---|
| SUM | =GROUPBY(Region,Sales,SUM) | North 27000; South 17000; West 22000; Total 66000 |
| COUNT | =GROUPBY(Region,Sales,COUNT) | North 2; South 2; West 1; Total 5 |
| MAX | =GROUPBY(Region,Sales,MAX) | North 15000; South 9000; West 22000; Total 22000 |
Worked out step by step, not calculated here: GROUPBY needs the latest Microsoft 365.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Older Excel | Needs the latest Microsoft 365 |
| Totals row | Use total_depth 0 to remove it |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Dynamic arrays18 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.