Does GROUPBY create an interactive pivot table?

No. GROUPBY returns a formatted dynamic array table that groups and aggregates, but lacks pivot table features like drill-down or auto-refresh. For interactive pivots, use PIVOTBY or Insert > Pivot Table. GROUPBY is best for quick static summaries.

How do I sort the result by the aggregated values instead of the group field?

GROUPBY's sort_order sorts by row_fields only. Wrap it in SORT: =SORT(GROUPBY(A2:A7, C2:C7, "SUM"), 2, -1) to sort column 2 (the aggregated total) in descending order.

Can I use custom formulas for aggregation instead of built-in functions?

No—GROUPBY accepts only standard functions like SUM, AVERAGE, COUNT, MAX, and MIN. For custom aggregation logic, use REDUCE, BYROW, or SUMPRODUCT with manual grouping helpers.

What causes #SPILL! when my GROUPBY formula looks correct?

The result array needs empty cells to expand into. Delete or move content to the right and below your formula. If the formula is in a cell surrounded by data, try placing it in an area with free space.

GROUPBY function

GROUPBY groups data by one or more fields and applies an aggregation function, returning a dynamic array table with subtotals and a grand total row.

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

Generate a GROUPBY formula

Describe what you need. The generator will reach for GROUPBY where GROUPBY is the right tool, and tell you when it is not.

Arguments

How GROUPBY reads its arguments

Argument	Required	Description
row_fields	Required	One or more columns (as arrays or ranges) that define the grouping levels; rows with identical values in these fields are combined into groups.
values	Required	The column(s) containing data to aggregate; must have the same number of rows as row_fields, or #N/A is returned.
function	Required	A text string naming an aggregation function ("SUM", "AVERAGE", "COUNT", "COUNTA", "MAX", "MIN", "PRODUCT", "STDEV", "STDEVP", "VAR", "VARP"); invalid names return #VALUE!.
field_headers	Optional	Optional; labels for row_fields columns displayed as the header row; if omitted, no headers appear above group levels.
total_depth	Optional	Optional; an integer specifying which grouping level gets subtotals (1 for innermost, 2 for next level up); if omitted, only the grand total row displays.
sort_order	Optional	Optional; 1 for ascending or -1 for descending sort of row_fields; pass an array of 1 or -1 per field for multi-level sorts; if omitted, groups appear in data order.
filter_array	Optional	Optional; a TRUE/FALSE array (one per row) that includes only rows marked TRUE; if omitted, all rows participate in the grouping.

Returns

A dynamic array table with group headers, aggregated values, optional subtotals, and a grand total row.

Availability

Excel: 365 (2024+) · Google Sheets: Not available

Worked examples

1. Sum on-hand inventory by SKU

SKU	Warehouse	On Hand	Reorder Point	Cost
A001	North	150	50	25
A001	South	200	75	25
B002	North	30	100	45.5
B002	South	80	100	45.5
C003	North	500	200	12
C003	South	420	200	12

=GROUPBY(A2:A7, C2:C7, "SUM", {"SKU", "Total On Hand"})

Result: SKUTotal On HandA001350B002110C003920Grand Total1380

GROUPBY identifies all rows with the same SKU and sums their On Hand quantities. A001 appears in both North and South (150 + 200 = 350); B002 totals 110; C003 totals 920. The Grand Total row sums all groups to 1380.

2. Count warehouses per SKU

SKU	Warehouse	On Hand	Reorder Point	Cost
A001	North	150	50	25
A001	South	200	75	25
B002	North	30	100	45.5
B002	South	80	100	45.5
C003	North	500	200	12
C003	South	420	200	12

=GROUPBY(A2:A7, A2:A7, "COUNTA", {"SKU", "Warehouse Count"})

Result: SKUWarehouse CountA0012B0022C0032Grand Total6

COUNTA counts non-empty cells per group, so each SKU (A001, B002, C003) shows a count of 2, reflecting that each is stocked in two warehouses. The grand total of 6 reflects all data rows.

3. Average product cost by warehouse

SKU	Warehouse	On Hand	Reorder Point	Cost
A001	North	150	50	25
A001	South	200	75	25
B002	North	30	100	45.5
B002	South	80	100	45.5
C003	North	500	200	12
C003	South	420	200	12

=GROUPBY(B2:B7, E2:E7, "AVERAGE", {"Warehouse", "Avg Cost"}, 1)

Result: WarehouseAvg CostNorth27.5South27.5Grand Total27.5

GROUPBY groups by Warehouse and calculates the average Cost. North holds A001 ($25), B002 ($45.50), and C003 ($12), averaging to $27.5; South holds the same SKUs at identical costs. The total_depth of 1 enables subtotals at the warehouse level if deeper nesting were present.

Common errors

Which GROUPBY error are you seeing?

Error	Why it happens	How to fix it
#SPILL!	The result array exceeds available space or adjacent cells are occupied, preventing the dynamic array from spilling into its intended range.	Clear or delete content in the cells where the result would spill, or move the GROUPBY formula to a location with more empty space to the right and below.
#VALUE!	The function argument is invalid—typically a misspelled aggregation name (e.g., "SUMM" instead of "SUM"), mismatched dimensions between row_fields and values, or an invalid sort_order value outside {-1, 1}.	Verify the function name is spelled correctly, ensure row_fields and values contain the same number of rows, and use only 1 or -1 for sort_order values.
#N/A	The row_fields or values range is empty, or their row counts do not match (e.g., row_fields spans 5 rows but values spans 6).	Confirm both row_fields and values reference identical numbers of rows, and that neither range is blank.

Tips and when to use something else

Use GROUPBY for quick summary tables and subtotals; it replaces manual pivot-table creation for simple one-off aggregations.
The sort_order parameter sorts only by row_fields, not by aggregated values. To sort results by the computed total, wrap GROUPBY in SORT: =SORT(GROUPBY(…), 2, -1).
For single aggregated values (not group breakdowns), use SUMIF, AVERAGEIF, or COUNTIF instead—they're simpler and more efficient than GROUPBY.
Multi-level grouping works by passing multiple columns to row_fields; total_depth then specifies which levels display subtotals (1 = innermost, 2 = next level up).

Related functions

SORTSORT arranges array data in ascending or descending order by a specified column, returning a sorted copy as a dynamic array.SORTBYSORTBY reorders an array based on one or more sort columns, returning rows in the order specified by your criteria—perfect when you need multi-column sorting.PIVOTBYPIVOTBY creates a dynamic pivot table by grouping source data along rows and columns, then applying an aggregation function.FILTERReturns filtered rows from an array based on a boolean criteria array, retaining the original order and all columns.SUMIFSUMIF returns the sum of cells in a range that meet a specified criteria, useful for totaling data filtered by a single condition.

Frequently asked questions

Does GROUPBY create an interactive pivot table?: No. GROUPBY returns a formatted dynamic array table that groups and aggregates, but lacks pivot table features like drill-down or auto-refresh. For interactive pivots, use PIVOTBY or Insert > Pivot Table. GROUPBY is best for quick static summaries.
How do I sort the result by the aggregated values instead of the group field?: GROUPBY's sort_order sorts by row_fields only. Wrap it in SORT: =SORT(GROUPBY(A2:A7, C2:C7, "SUM"), 2, -1) to sort column 2 (the aggregated total) in descending order.
Can I use custom formulas for aggregation instead of built-in functions?: No—GROUPBY accepts only standard functions like SUM, AVERAGE, COUNT, MAX, and MIN. For custom aggregation logic, use REDUCE, BYROW, or SUMPRODUCT with manual grouping helpers.
What causes #SPILL! when my GROUPBY formula looks correct?: The result array needs empty cells to expand into. Delete or move content to the right and below your formula. If the formula is in a cell surrounded by data, try placing it in an area with free space.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17