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
Calculate totals from observations, not displayed averages
=PIVOTBY(A1:A6,B1:B6,C1:C6,AVERAGE)The East/Q1 observations are 12 and -2, so their mean is 5. The grand mean is (12+8-2+5+0)/5=4.6, not the average of the displayed group means. The first input row is detected as headings. Grand totals use the original records. This result is a formula spill rather than a native PivotTable.
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(A1:A6,B1:B6,C1:C6,AVERAGE)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.