GROUPBY groups rows that share the same values and calculates a summary (such as sum, average, or count) for each group, returning a spillable table. It works like a formula-driven PivotTable that updates automatically when the source data changes.
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])What does GROUPBY do?
GROUPBY is a dynamic array function that lets you summarise a table in a single formula. You tell it which column (or columns) to group by, which values to aggregate, and which function to apply—such as SUM, AVERAGE, or COUNT—and it returns a compact table of results. It is the formula equivalent of building a PivotTable, but without the clicking, and it refreshes automatically when the source data changes.
Before GROUPBY, getting a grouped summary usually meant creating a PivotTable, using a combination of UNIQUE and SUMIF, or writing a long array formula. GROUPBY replaces all of that with one readable formula. Because the result spills into neighbouring cells, it is ideal for dashboards, reports, and any sheet where you want a live summary that stays in sync with the data.
Imagine you have a sales table with region, product, and amount columns. You want to see total sales by region, average sale by product, and a count of orders per region—all on the same sheet. With GROUPBY you write three short formulas, and if a new row is added, the summaries update instantly. No PivotTable refresh, no dragging formulas down.
When to use GROUPBY
- Summarising sales or expenses by category, region, or month.
- Counting orders or attendees per group without a PivotTable.
- Calculating averages, maximums, or minimums for each product or department.
- Building a live dashboard that updates when source data changes.
- Producing a quick grouped report that can be sorted or filtered inside the formula.
- Replacing helper columns and SUMIF/COUNTIF formulas with one dynamic formula.
GROUPBY syntax
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])| Argument | Required? | What to enter |
|---|---|---|
row_fields | Required | The column (or columns) that contain the values to group by. Example: A2:A10 for a Region column. |
values | Required | The column (or columns) of numbers to summarise. Example: B2:B10 for a Sales column. |
function | Required | The aggregation to apply to each group, such as SUM, AVERAGE, COUNT, MAX, or MIN. Example: SUM. |
field_headers | Optional | Controls whether headers are shown and whether they come from the data. Use 3 to show headers from the data, 1 to show generic headers, 0 for no headers. Example: 3. |
total_depth | Optional | Controls grand totals and subtotals. 0 = no totals, 1 = grand total at bottom, 2 = grand total and subtotals, -1 = grand total at top. Example: 1. |
sort_order | Optional | Sorts the result by a column index; negative numbers sort descending. Example: 2 sorts by the second column ascending, -2 descending. |
filter_array | Optional | A TRUE/FALSE column that includes only rows where TRUE. Example: C2:C10>100 to include only values above 100. |
field_relationship | Optional | Use 0 (default) when grouping columns come from the same table; use 1 when they come from a related table. Example: 0. |
What it returns: A dynamic array (spill range) containing one row per unique group, with the grouped values in the first column(s) and the aggregated results in the last column(s). The result includes headers and optional totals depending on the arguments.
GROUPBY examples
1 Total sales by region
This example groups a simple sales table by Region and sums the Sales column. The formula uses A2:A6 as the row fields and B2:B6 as the values, applies SUM, and shows headers from the data with 3. It returns one row per region with the total sales for that region. The result spills into two columns: Region and Sum of Sales.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | North | ||
| 3 | South | 800 | |||
| 4 | North | 600 | |||
| 5 | East | 400 | |||
| 6 | South | 300 | |||
| 7 |
2 Average sale by product
Here we group by Product and calculate the average sales amount for each product. The formula uses A2:A6 as row fields, B2:B6 as values, and AVERAGE as the function. With 3 for headers, the output includes a header row. The result shows each product once with its average sale, which helps compare typical performance rather than totals.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | |||
| 2 | Widget | 100 | Widget | ||
| 3 | Gadget | 200 | |||
| 4 | Widget | 300 | |||
| 5 | Gadget | 400 | |||
| 6 | Widget | 500 | |||
| 7 |
3 Count orders by region with a grand total
This example counts how many orders appear for each region and adds a grand total row. The formula groups by Region (A2:A6), counts the Order IDs in B2:B6 using COUNT, shows headers with 3, and sets total_depth to 1 to add a grand total at the bottom. The result lists each region with its order count and a final Total row showing the overall count.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | OrderID | |||
| 2 | North | 101 | North | ||
| 3 | South | 102 | |||
| 4 | North | 103 | |||
| 5 | East | 104 | |||
| 6 | South | 105 | |||
| 7 |
4 Sum sales by region, sorted descending
This example sums sales by region and sorts the result from highest to lowest. The formula uses A2:A6 for regions, B2:B6 for sales, SUM, headers 3, no totals (0), and a sort order of -2 to sort by the second column (the sum) in descending order. The output shows the highest-selling region first, making it easy to spot the top performer.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | North | ||
| 3 | South | 800 | |||
| 4 | North | 600 | |||
| 5 | East | 400 | |||
| 6 | South | 300 | |||
| 7 |
5 Filtered sum: only sales above 500
This example filters the data before grouping, so only rows with sales greater than 500 are included. The formula groups by Region (A2:A6), sums B2:B6, uses SUM, shows headers with 3, and applies a filter array B2:B6>500. The result includes only regions that have at least one sale above 500, and the sums reflect only those qualifying rows.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | North | ||
| 3 | South | 800 | |||
| 4 | North | 600 | |||
| 5 | East | 400 | |||
| 6 | South | 300 | |||
| 7 |
Common GROUPBY errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The function argument is not a valid aggregation, or the values range contains text where a number is required. | Use a supported function such as SUM, AVERAGE, or COUNT, and make sure the values column contains numbers. |
| #CALC! | The result is empty because the filter removed all rows, or the ranges have mismatched sizes. | Check that row_fields and values have the same number of rows, and adjust the filter so at least one row qualifies. |
| #SPILL! | There is not enough empty space below or to the right of the target cell for the result to spill. | Clear the cells in the spill range or move the formula to an area with enough free space. |
| #NAME? | The function name is misspelled or the version of Excel does not support GROUPBY. | Check the spelling and ensure you are using a version of Excel that includes GROUPBY (Microsoft 365). |
Tips for using GROUPBY
- Use structured references like
Table1[Region]instead of cell ranges so the formula expands automatically when rows are added. - Set
field_headersto3to use your data's own headers; use1if you want generic labels like 'Region' and 'Sum of Sales'. - Use
total_depthto add grand totals without writing a separate SUM formula. - Sort inside the formula with
sort_orderto keep the output ranked without manual sorting. - Apply
filter_arrayto exclude blanks or zero values before grouping, which keeps the summary clean. - GROUPBY spills, so leave enough empty cells to the right and below the target cell.
Frequently asked questions
What is the difference between GROUPBY and a PivotTable?
GROUPBY is a formula that returns a dynamic array and updates automatically when data changes. A PivotTable is an interactive object that you refresh manually. GROUPBY is ideal for dashboards and formulas; PivotTables offer more drag-and-drop flexibility.
Can GROUPBY group by more than one column?
Yes. Pass a range with multiple columns as row_fields, such as A2:B10, and GROUPBY will create a group for each unique combination of values.
Does GROUPBY work in Excel 2019 or Excel 2021?
No. GROUPBY is available only in Microsoft 365 and Excel for the web. Older perpetual versions do not have this function.
How do I add a grand total with GROUPBY?
Set the total_depth argument to 1 for a grand total at the bottom, 2 for grand total and subtotals, or -1 for a grand total at the top.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. GROUPBY reference