Summary Tables
PIVOTBY
Creates pivot-style column groups from row fields, column fields, and aggregated values.
Syntax
=PIVOTBY(row_fields, column_fields, values, function_name, [field_headers], [row_total_depth], [row_sort_order], [column_total_depth], [column_sort_order], [filter_array])Parameters
NameTypeDescription
Name
row_fieldsTyperange
DescriptionOne or more columns used for row grouping.
Name
column_fieldsTyperange
DescriptionOne or more columns used to create pivot columns.
Name
valuesTyperange
DescriptionOne or more columns containing values to aggregate.
Name
function_nameTypetext or range
DescriptionAggregation name such as "SUM", "COUNT", or "AVERAGE".
Name
field_headers?Typeboolean, number, or text
DescriptionTRUE treats the first row of each input range as headers.
Name
row_total_depth?Typeboolean, number, or text
DescriptionTRUE adds a Grand Total row.
Name
row_sort_order?Typetext, number, or boolean
DescriptionUse "asc", "desc", "value_asc", "value_desc", or FALSE for input order.
Name
column_total_depth?Typeboolean, number, or text
DescriptionTRUE adds Total columns.
Name
column_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 pivot table with row groups, dynamic column groups, aggregate values, and optional totals.
Example
=PIVOTBY(A1:A6, B1:B6, C1:C6, "SUM", TRUE, TRUE, "asc", TRUE)Result: Q1/Q2 revenue columns by region with totals
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.