Summary Tables
GROUPBY
Groups rows by one or more fields and spills a dynamic aggregate table.
Syntax
=GROUPBY(row_fields, values, function_name, [field_headers], [total_depth], [sort_order], [filter_array])Parameters
NameTypeDescription
Name
row_fieldsTyperange
DescriptionOne or more columns used to group rows.
Name
valuesTyperange
DescriptionOne or more columns containing values to aggregate.
Name
function_nameTypetext or range
DescriptionAggregation name such as "SUM", "AVERAGE", "TEXTJOIN: | ", or "SUM,COUNT".
Name
field_headers?Typeboolean, number, or text
DescriptionTRUE treats the first row as headers; FALSE treats every row as data.
Name
total_depth?Typeboolean, number, or text
DescriptionTRUE adds a Grand Total row.
Name
sort_order?Typetext, number, or boolean
DescriptionUse "asc", "desc", "value_asc", "value_desc", or FALSE for input order.
Name
filter_array?Typerange
DescriptionOptional truthy/falsey range used to include rows.
Returns
A spilled summary table with group columns, aggregate columns, and optional grand total row.
Example
=GROUPBY(A1:A6, C1:C6, "SUM", TRUE, TRUE)Result: East 215; West 75; Grand Total 290
For a Region and Revenue table with headers in row 1.
Notes
Set field_headers to TRUE when the first row contains headers, FALSE when it does not, or leave it blank for auto-detection.
Supported aggregations include SUM, COUNT, COUNTA, AVERAGE, MIN, MAX, MEDIAN, TEXTJOIN, UNIQUE, COUNTUNIQUE, FIRST, LAST, and PERCENTILE:0.9.
Apps Script custom functions cannot execute arbitrary Sheets LAMBDA callbacks, so aggregations are selected by name.