Back to functions
Lookup & Reference2026-11-161 related article

GROUPBY Function in Excel

Group matching labels and summarize their values in a formula result, with optional headers and totals.

Syntax

GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])
Table of Contents

Arguments

row_fields

Required

Column-oriented labels to group by. Supply multiple columns for multiple grouping levels.

values

Required

Column-oriented values to summarize, aligned with the grouping rows. Multiple columns produce multiple summaries.

function

Required

An aggregation function such as SUM, a custom LAMBDA, or a vector of aggregation functions. The examples use LAMBDA(x,SUM(x)).

field_headers

Optional

Omit for automatic detection. Use 0 for inputs without headers, 1 for inputs with headers that should be hidden, 2 to generate headers, or 3 to show headers supplied with the inputs.

total_depth

Optional

Omit for automatic totals. Use 0 for none, 1 for a grand total, or 2 for grand totals and subtotals. Negative values put totals at the top. Subtotals require multiple grouping columns; larger depths require enough grouping levels.

sort_order

Optional

A scalar gives a one-based column position to sort by, counting row_fields followed by values. A negative position reverses the order. A vector can specify multiple sort positions within row_fields only.

filter_array

Optional

A column-oriented Boolean array aligned with the input rows. TRUE includes a row in the summary.

field_relationship

Optional

Use 0 for hierarchical grouping (the default) or 1 for a table relationship with independent field sorting and no subtotals.

What it returns

A summary grouped down one axis, with optional headers, sorting, filtering, and totals.

What GROUPBY does

GROUPBY collects rows with matching labels and applies a calculation to each group. For example, several sales for Binder become one Binder row containing their total. The result recalculates as a worksheet formula.

Use PIVOTBY when you also want a second field, such as Region, spread across columns.

Practical examples

Total sales for each product

Create an Excel Table named Sales in A4:C10 with the following data. Keep the Table's Total Row off, and enter the sales amounts as numbers.

ProductRegionSales
BinderNorth60
BinderSouth40
BinderNorth25
NotebookNorth30
NotebookSouth20
NotebookSouth15

Keep E4:F7 clear and enter this in E4:

=GROUPBY(Sales[[#All],[Product]],Sales[[#All],[Sales]],LAMBDA(x,SUM(x)),3,1,1)
Formula

The expected result is:

Product     Sales
Binder      125
Notebook    65
Total       190
Example

LAMBDA(x,SUM(x)) adds the amounts passed to it for each group. Binder contributes 60 + 40 + 25 = 125; Notebook contributes 30 + 20 + 15 = 65.

The #All references include headers, and 3 displays them in the result. The next 1 requests the grand total; the last 1 sorts by the first grouping column. Keep the Table Total Row off so these references don't also include a source total.

Show product totals without a grand-total row

Using the same six-row Sales Table, replace the formula in E4 with:

=GROUPBY(Sales[[#All],[Product]],Sales[[#All],[Sales]],LAMBDA(x,SUM(x)),3,0,1)
Formula

The expected result is:

Product     Sales
Binder      125
Notebook    65
Example

Only total_depth changes, from 1 to 0. The two product sums are unchanged; the grand-total row is omitted. The sales summary walkthrough shows how Table references include a newly appended sale.

Common mistakes and notes

Match the header setting to the source

Sales[Product] supplies data rows, while Sales[[#All],[Product]] includes the header and any enabled Total Row. The examples deliberately include headers and use field_headers equal to 3. Don't copy that setting unchanged into a formula whose input has no headers.

A subtotal needs more than one grouping level

The examples have only Product as a grouping field. To use subtotals, supply at least two grouping columns and retain the hierarchical field relationship. The table relationship setting, 1, doesn't support subtotals.

A valid summary can still omit an amount

SUM ignores text and empty cells in its range input. Check that imported sales are numeric before trusting the totals. Align the grouping, value, and optional filter arrays to the same source rows.

Check function availability and leave room for the result

Microsoft's Applies To list names Excel for Microsoft 365. The explicit LAMBDA aggregation used here worked in the tested Excel for Microsoft 365 for Mac 16.114 installation. Microsoft also documents bare SUM as an aggregation argument, but that form returned #NAME? on that installation. This is a build-specific observation, not a claim that bare SUM fails everywhere.

Put the formula outside the source Table and keep enough neighboring cells clear for its output to grow. A new product needs another result row.

Related functions

Related articles

Deep dives, troubleshooting guides, and practical examples that use GROUPBY.

Official documentation