17. GROUPBY and PIVOTBY

To get totals by group you used to build a PivotTable or write a SUMIF for every row. Microsoft 365 has two functions that do it with a single formula, and the result spills by itself, like UNIQUE and FILTER.

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

• row_fields is what to group by (region, manager);
• values is what to aggregate (amounts);
• function is how to aggregate. Write it without parentheses: SUM, AVERAGE, COUNT, MAX, MIN. You can also pass your own LAMBDA(x, …);
• field_headers: if omitted, Excel decides for itself: text above numbers counts as a header and is shown. 0 means no headers, 1 means headers that are not shown, 2 means no headers but generate them, 3 means headers that are shown;
• total_depth: 1 (default) adds a total row at the bottom, 0 means no total, −1 puts the total on top;
• sort_order is a column number of the result; a minus sign sorts descending: -2 sorts by the second column from largest to smallest;
• filter_array is an array of TRUE/FALSE of the same height: which rows to take.

PIVOTBY(row_fields, col_fields, values, function, …) does the same but groups in two directions at once, giving you a small PivotTable. If a combination does not exist in the data (Denver sold no sugar), the cell stays empty.

The total row is labeled Total. Practice grid limits: subtotals by level (total_depth = 2) are not supported, and PIVOTBY takes one row field, one column field and one column of values.