ExcelExcel Microsoft 365+

GROUPBY function in Excel

Helps a user group, aggregate, sort, and filter data based on the fields you specify

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

GROUPBY syntax and parameters

Eight arguments, three required.

ParameterTypeUseDescription

row_fieldsarrayRequiredA column-oriented array or range that contains the values which are used to group rows and generate row headers.The array or range may contain multiple columns. If so, the output will have multiple row group levels.

valuesarrayRequiredA column-oriented array or range of the data to aggregate.The array or range may contain multiple columns. If so, the output will have multiple aggregations.

functionanyRequiredAn explicit or eta reduced lambda (SUM, PERCENTOF, AVERAGE, COUNT, etc.) that is used to aggregate values.A vector of lambdas can be provided. If so, the output will have multiple aggregations. The orientation of the vector will determine whether they are laid out row- or column-wise.

field_headersnumberOptionalA number that specifies whether the row_fields and values have headers and whether field headers should be returned in the results. The possible values are:Missing: Automatic (default) 0: No 1: Yes and don't show 2: No but generate 3: Yes and showNote: Automatic assumes the data contains headers based on the values argument. If the 1st value is text and the 2nd value is a number, then the data is assumed to have headers. Fields headers are shown if there are multiple row or column group levels.

total_depthnumberOptionalDetermines whether the row headers should contain totals. The possible values are:Missing: Automatic: Grand totals and, where possible, subtotals (default) 0: No Totals 1: Grand Totals 2: Grand and Subtotals -1: Grand Totals at Top -2: Grand and Subtotals at TopNote: For subtotals, fields must have at least 2 columns. Numbers greater than 2 are supported provided field has sufficient columns.

sort_orderanyOptionalA number indicating how rows should be sorted. Numbers correspond with columns in row_fields followed by the columns in values. If the number is negative, the rows are sorted in descending/reverse order.A vector of numbers can be provided when sorting based on only row_fields.

filter_arrayarrayOptionalA column-oriented 1D array of Booleans that indicate whether the corresponding row of data should be considered.Note: The length of the array must match the length of those provided to row_fields.

field_relationshipnumberOptionalSpecifies the relationship fields when multiple columns are provided to row_fields. The possible values are:0: Hierarchy (default) 1: TableWith a Hierarchy field relationship (0), sorting of later field columns takes into account the hierarchy of earlier columns.With a Table field relationship (1), sorting of each field column is done independently. Subtotals are not supported as they rely on the data having a hierarchy.

row_fieldsarrayRequired
A column-oriented array or range that contains the values which are used to group rows and generate row headers.The array or range may contain multiple columns. If so, the output will have multiple row group levels.
valuesarrayRequired
A column-oriented array or range of the data to aggregate.The array or range may contain multiple columns. If so, the output will have multiple aggregations.
functionanyRequired
An explicit or eta reduced lambda (SUM, PERCENTOF, AVERAGE, COUNT, etc.) that is used to aggregate values.A vector of lambdas can be provided. If so, the output will have multiple aggregations. The orientation of the vector will determine whether they are laid out row- or column-wise.
field_headersnumberOptional
A number that specifies whether the row_fields and values have headers and whether field headers should be returned in the results. The possible values are:Missing: Automatic (default) 0: No 1: Yes and don't show 2: No but generate 3: Yes and showNote: Automatic assumes the data contains headers based on the values argument. If the 1st value is text and the 2nd value is a number, then the data is assumed to have headers. Fields headers are shown if there are multiple row or column group levels.
total_depthnumberOptional
Determines whether the row headers should contain totals. The possible values are:Missing: Automatic: Grand totals and, where possible, subtotals (default) 0: No Totals 1: Grand Totals 2: Grand and Subtotals -1: Grand Totals at Top -2: Grand and Subtotals at TopNote: For subtotals, fields must have at least 2 columns. Numbers greater than 2 are supported provided field has sufficient columns.
sort_orderanyOptional
A number indicating how rows should be sorted. Numbers correspond with columns in row_fields followed by the columns in values. If the number is negative, the rows are sorted in descending/reverse order.A vector of numbers can be provided when sorting based on only row_fields.
filter_arrayarrayOptional
A column-oriented 1D array of Booleans that indicate whether the corresponding row of data should be considered.Note: The length of the array must match the length of those provided to row_fields.
field_relationshipnumberOptional
Specifies the relationship fields when multiple columns are provided to row_fields. The possible values are:0: Hierarchy (default) 1: TableWith a Hierarchy field relationship (0), sorting of later field columns takes into account the hierarchy of earlier columns.With a Table field relationship (1), sorting of each field column is done independently. Subtotals are not supported as they rely on the data having a hierarchy.

GROUPBY examples

Formulas you'll actually reuse.

Revenue totalled by region — a one-formula pivot:
```
=GROUPBY(A2:A200, C2:C200, SUM)
```
Average deal size by region and rep, no totals, largest first (-3 = the third output column, descending):
```
=GROUPBY(A2:B200, C2:C200, AVERAGE, 0, 0, -3)
```

GROUPBY in Google Sheets

No direct equivalent — here's the way around.

Try GROUPBY in the playground

Edit the example — nothing to install.

Preloaded with the GROUPBY formula from Example 1 — change anything and watch it respond.

Loading the editor…

GROUPBY errors

What they mean — and the fixes.

#VALUE!
row_fields and values don't have the same number of rows — both must be column-oriented and equal in height.

Related functions

More ways Excel gets this done.