Practice free

PIVOTBY function

PIVOTBY builds a PivotTable-style summary with a formula, grouping down rows and across columns, and recalculates when the data changes. It returns a spilled cross-tab with grand totals by default.

Example

Read it as a sentence: build a summary table that adds up the Qty values in D2:D7, with Category in C2:C7 down the side and Item in B2:B7 across the top.

Practice this in the app

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])

Arguments in [square brackets] are optional.

row_fields (required)
Column(s) that form the row groups.
col_fields (required)
Column(s) that form the column groups.
values (required)
The values to aggregate.
function (required)
The aggregation such as SUM or COUNT, or a LAMBDA.
field_headers (optional)
Whether inputs include headers.
row_total_depth (optional)
0 none, 1 grand total (default), 2 plus subtotals.
row_sort_order (optional)
Sort order for rows.
col_total_depth (optional)
Same for columns.
col_sort_order (optional)
Sort order for columns.
filter_array (optional)
TRUE/FALSE array to include only some rows.
relative_to (optional)
How to express values as a percentage, e.g. of column or row totals.

Returns: A spilled cross-tab summary.

One PIVOTBY formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=PIVOTBY(Items!C2:C7,Items!B2:B7,Items!D2:D7,SUM) Regions x names
=PIVOTBY(Items!C2:C7,Items!C2:C7,Items!D2:D7,COUNT) Same field both axes
=PIVOTBY(Items!C1:C7,Items!B1:B7,Items!D1:D7,SUM,3,0,,0) Headers, no totals
=PIVOTBY(Items!C2:C7,Items!B2:B3,Items!D2:D7,SUM) #VALUE! Mismatched sizes

The Items sheet these formulas read

Watch out for

  • Includes grand totals by default.
  • Without headers the top-left cell is blank.
  • Pass the function name bare, such as SUM.

Where it works

Excel
PIVOTBY is only in Excel for Microsoft 365 and Excel for the web. Recent 365 addition; argument list written of the rollout, so verify against your build.
Google Sheets
No direct equivalent: `=QUERY(range,"select Col1, sum(Col3) group by Col1 pivot Col2")`. Use a pivot table or QUERY with PIVOT. Newer Sheets versions may offer a similar function; unsure.

Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .