What is the syntax for GROUPBY in Excel?

The syntax for GROUPBY is: =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])

What are common errors with GROUPBY?

#VALUE! if function is invalid. #CALC! if arrays are mismatched

How do I use the GROUPBY function?

To use GROUPBY, type =GROUPBY( in a cell and provide the required arguments. Excel will show you the syntax and parameter hints as you type. Make sure to close the parentheses and press Enter to execute the function.

Why is GROUPBY not working in my Excel?

Common issues with GROUPBY include #VALUE! if function is invalid; #CALC! if arrays are mismatched. Start by checking the syntax =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array]), then verify each referenced cell or range.

Lookup & Reference

GROUPBY Function in Excel

Groups data by row values and aggregates associated values.

Syntax

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

Arguments

row_fields (required): Columns to group by
values (required): Values to aggregate
function (required): Aggregation function (SUM, AVERAGE, COUNT, etc.)
field_headers (optional): 0=no headers, 1=yes, 2=no but generate
total_depth (optional): Subtotal levels (-1=grand total, 0=none, 1+=levels)
sort_order (optional): Sort order for results
filter_array (optional): Boolean array to filter rows

Examples

=GROUPBY(A2:A100, C2:C100, SUM) - Sum values by category - Result: Grouped sums
=GROUPBY(A2:B100, D2:D100, AVERAGE, 1, 1) - Average with headers and subtotals - Result: Grouped averages

When to use GROUPBY

Use GROUPBY when you need to retrieve values from a table or range based on a key.
Confirm data types match (text vs number) and trim spaces before lookups.
Pair with [IFERROR](/functions/iferror/) or [IFNA](/functions/ifna/) for cleaner reports.
See the [lookup functions guide](/guides/excel-lookup-functions-guide/) for VLOOKUP, XLOOKUP, and INDEX MATCH paths.

Common GROUPBY mistakes

#N/A often means the key is missing or exact match is wrong — see [#N/A error](/errors/na/).
Lock table references with $ when copying formulas down a column.
Compare legacy vs modern lookups in [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/).

Common errors

#VALUE! if function is invalid
#CALC! if arrays are mismatched

Use cases

Data summarization
Sales reports
Category analysis
Dynamic grouping

Frequently asked questions

What is the GROUPBY function in Excel? GROUPBY groups data by row values and aggregates associated values. The syntax is =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array]). Typical use cases include data summarization, sales reports, category analysis.
How do I use the GROUPBY function? To use GROUPBY, type =GROUPBY( in a cell and provide the required arguments. Excel will show you the syntax and parameter hints as you type. Make sure to close the parentheses and press Enter to execute the function.
Why is GROUPBY not working in my Excel? Common issues with GROUPBY include #VALUE! if function is invalid; #CALC! if arrays are mismatched. Start by checking the syntax =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array]), then verify each referenced cell or range.

Editorial review

Reviewed by Excel.Directory Editorial Team. Updated May 2026.