Purpose
Return value
Syntax
=GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])- row_fields - The values for grouping.
- values - The values to aggregate.
- function - The calculation to run when aggregating.
- field_headers - [optional] 0 = No, 1 = Yes, don't show, 2 = No, generate, 3 = Yes, show.
- total_depth - [optional] Totals and subtotals. 0 = No, 1 = Grand Totals, 2 = Both, -1 = Grand Totals at top, -2 = Both at top.
- sort_order - [optional] Sort by index number. Negative numbers = descending order.
- filter_array - [optional] Logic to remove specific rows.
How to use
The GROUPBY function is designed to summarize data by grouping rows and aggregating values. The result is a dynamic summary table created with a single formula. The output from the GROUPBY function is similar to the output from a Pivot Table but without formatting. The summary returned by the GROUPBY function is fully dynamic and will immediately recalculate when source data changes.
Basic Example
The GROUPBY function takes seven arguments but only the first three are required. In the worksheet shown below, we use the GROUPBY function to summarize sales by city. The formula in cell F5 is:
=GROUPBY(B5:B16,D5:D16,SUM)
For this example, GROUPBY is configured with three arguments as follows:
- row_fields - provided as the range B5:B16, which contains city names.
- values - provided as the range D5:D16, which contains sales amounts.
- function - provided as SUM, the calculation to perform during aggregation.
With the inputs above, the GROUBY function summarizes sales by city and outputs the table in F4:G8 in one step. Notice that we have not included the header row in row_fields or values, because we are not asking GROUPBY to generate a header automatically. Instead, we have manually entered the header row in F4:G4. Note also that GROUPBY includes a Total row by default.
Related functions
PIVOTBY Function
The Excel PIVOTBY function is designed to summarize data by grouping rows and columns. The result is a dynamic summary table created with a single formula.
