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.
Using the GROUPBY function
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 GROUPBY 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.
Example - GROUPBY with field headers
The fourth argument in GROUPBY, field_headers, controls how the header row is handled when it is part of the data provided to the GROUPBY function. The are four possible values for field_headers:
| Value | Meaning |
|---|---|
| 0 | Headers are not provided in the data (default). |
| 1 | Headers are included, but should not be displayed. |
| 2 | Headers are not included but should be generated. |
| 3 | Headers are included and should be displayed. |
The field_header argument is optional and defaults to zero (0), which means that headers are not provided in the data. You can see this behavior in the example above, where the ranges used for row_fields and values do not include the header row. In cases where the data includes a header row that you want to use, set the value of field_headers to 3. You can see how this works in the worksheet below, where the formula in cell F4 is:
=GROUPBY(B4:B16,D4:D16,SUM,3)
The inputs to GROUBY are as follows:
- row_fields - provided as the range B4:B16, which contains city names, plus the header row.
- values - provided as the range D4:D16, which contains sales amounts, plus the header row.
- function - provided as SUM, the calculation to perform during aggregation.
- field_headers - provided as 3, since the data now includes a header row that should be displayed.
Note that the data in this example is exactly the same as the previous example. However, the ranges used for row fields and values now include the headers in row 4. As a result, the formula is entered in cell F4, since that row is already formatted as a header.
GROUPBY function examples
Sum if with multiple ranges
Sum if multiple columns
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.
