PIVOTBY function
Summarize values across row and column groups.
Group source records by row and column fields, then calculate an aggregate for each intersection. One axis can be omitted for a one-dimensional summary. The browser examples cover filters, sorting, record-based totals, reducer vectors and custom lambdas. A formula spill and a native PivotTable are separate objects. Native Excel recalculation and full layout/coercion parity remain unverified.
Syntax
PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])Square brackets indicate optional arguments.
Arguments
| Argument | Required | Meaning |
|---|---|---|
| row_fields | Yes | Column or columns whose values define row groups. |
| col_fields | Yes | Column or columns whose values define column groups. |
| values | Yes | Numeric data to summarize, aligned with the grouping fields. |
| function | Yes | The aggregation function or LAMBDA used for each group. |
| field_headers | No | Controls whether input headers exist and whether headers are returned. |
| row_total_depth | No | Controls subtotal and grand-total levels for row groups. |
| row_sort_order | No | Controls how row groups are sorted. |
| col_total_depth | No | Controls subtotal and grand-total levels for column groups. |
| col_sort_order | No | Controls how column groups are sorted. |
| filter_array | No | A Boolean filter aligned with source records; only included records are summarized. |
| relative_to | No | Defines the denominator used for relative aggregation calculations. |
Try PIVOTBY
Express each intersection as a share of the grand total
=PIVOTBY(A2:A6,B2:B6,C2:C6,PERCENTOF,0,0,1,0,1,,2)relative_to=2 supplies all retained observations as the denominator set. Their sum is 23, so East/Q1 contributes 10/23. Results are decimal proportions; apply percentage formatting to display percentages. relative_to values 0 and 1 instead select column and row totals.
Inspect the current formula
Read its written arguments or observe actual operation values in a separate local calculation. This action neither applies a draft nor grades an answer.
Read the formula, one argument at a time
Explore the written function calls and their inputs. The calculation result belongs to the complete formula.
PIVOTBY · top-level call
=PIVOTBY(A2:A6,B2:B6,C2:C6,PERCENTOF,0,0,1,0,1,,2)Follow the cells behind a result
Read explicit local references and helper formulas using a separate calculation. Your draft stays in this field; the worksheet and practice checks do not change.
The worksheet runs locally. Published checks apply to the supplied example only. Native Excel and Google Sheets compatibility remain unverified.
Practice a complete task
Reference and compatibility
Original SheetTutor reference notes / Microsoft documentation. Calculation checks cover the supplied examples only. The browser worksheet does not implement every Excel feature, external service or connected data type.