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
Capture a LET parameter inside the group reducer
=LET(rate,2,PIVOTBY(A2:A6,B2:B6,C2:C6,LAMBDA(items,SUM(items)*rate),0,0,1,0,1))The local rate of 2 is captured by the custom LAMBDA. Each original subset is summed and then multiplied by that rate, so East/Q1 is (12-2)*2=20. Change rate to explore the rule; reset restores the original checked inputs.
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 · inside LET
=LET(rate,2,PIVOTBY(A2:A6,B2:B6,C2:C6,LAMBDA(items,SUM(items)*rate),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.