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
Cross-tabulate two fields with a formula spill
=PIVOTBY(A2:A6,B2:B6,C2:C6,SUM,0,0,1,0,1)East/Q1 totals 12-2=10; East/Q2 is 5; West/Q1 is a real zero; West/Q2 is 8. Explicit options disable input field headers and totals and sort both axes ascending. The three-by-three result is a formula spill, separate from an Excel PivotTable object. PIVOTBY needs a supported modern Excel version.
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,SUM,0,0,1,0,1)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.