Summarize data by grouping rows
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])| Parameter | Description |
|---|---|
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 exclude specific rows. |
field_relationship |
[optional] Field relationship when multiple columns are provided as row fields. 0 = Hierarchy (default), 1 = Table. |
The GROUPBY function takes eight arguments, but only the first three are required. In the worksheet below, we use the GROUPBY function to summarize Sa
=GROUPBY(B5:B16,D5:D16,SUM)The field_headers argument is optional. When field_headers is omitted, GROUPBY will automatically detect headers in the source data by testing values.
=GROUPBY(B4:B16,D4:D16,SUM,3) // field headers enabledThe third argument in GROUPBY is function, which specifies the calculation to perform when values are grouped. Available calculations include Excel fu
=GROUPBY(D4:D104,D4:D104,COUNTA,1)In the next example, we've changed the calculation to generate a total cost by meal, as seen below. The formula in G5 now looks like this:
=GROUPBY(D4:D104,E4:E104,SUM,1)It is possible to perform more than one calculation with GROUPBY, but it is not obvious how to do so, since the function argument accepts just one val
=GROUPBY(D4:D104,E4:E104,HSTACK(COUNT,SUM),1)Notice that the formula is now entered in cell G4, even though we have set field_headers to 1 (do not display). This is because GROUPBY automatically
=GROUPBY(D4:D104,E4:E104,HSTACK(COUNT,SUM,PERCENTOF),1)