Back to functions
Lookup & Reference2026-11-161 related article

PIVOTBY Function in Excel

Summarize values by row and column groups in a formula-generated cross-tab, with optional headers and totals.

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])
Table of Contents

Arguments

row_fields

Required

Column-oriented labels to group down the result. Multiple columns supply multiple grouping levels.

col_fields

Required

Column-oriented labels to group across the result. Multiple columns supply multiple grouping levels.

values

Required

Column-oriented values to summarize, aligned with both sets of grouping fields.

function

Required

An aggregation function such as SUM, a custom LAMBDA, or a vector of aggregation functions. The examples use LAMBDA(x,SUM(x)).

field_headers

Optional

Omit for automatic detection. Use 0 for inputs without headers, 1 for inputs with headers that should be hidden, 2 to generate headers, or 3 to show headers supplied with the inputs.

row_total_depth

Optional

Omit for automatic row totals. Use 0 for none, 1 for a grand total, or 2 for grand totals and subtotals. Negative values put totals at the top. Subtotals need multiple row grouping levels.

row_sort_order

Optional

A scalar gives a one-based position to sort rows by, counting row_fields followed by values. A negative position reverses order. A vector can specify multiple sort positions within row_fields only.

col_total_depth

Optional

Omit for automatic column totals. Use 0 for none, 1 for a grand total, or 2 for grand totals and subtotals. Negative values put totals on the left. Subtotals need multiple column grouping levels.

col_sort_order

Optional

A scalar gives a one-based position to sort columns by, counting col_fields followed by values. A negative position reverses order. A vector can specify multiple sort positions within col_fields only.

filter_array

Optional

A column-oriented Boolean array aligned with the input rows. TRUE includes a row in the summary.

relative_to

Optional

Selects the second input for a two-argument aggregation. Use 0 for column totals (default), 1 for row totals, 2 for the grand total, 3 for parent column totals, or 4 for parent row totals. It doesn't change the one-input SUM examples.

What it returns

A cross-tab of values aggregated by row and column groups, with optional headers and totals. The result is a worksheet array, not a PivotTable object.

What PIVOTBY does

PIVOTBY summarizes records at the intersection of two sets of groups. For a sales list, Product can run down the rows and Region across the columns, with each intersection showing the matching sales total. Use GROUPBY if you need only one total per product.

Use Excel for Microsoft 365 with PIVOTBY available. The platform labels above reproduce Microsoft's Support page, but its listing of older perpetual editions conflicts with the product team's release guidance. These examples don't establish support in Excel 2021 or Excel 2024. The baseline example was checked in Excel for Microsoft 365 for Mac 16.114.

Practical examples

Split product sales across regions

Create an Excel Table named Sales in A4:C10 using these records. Keep the Table's Total Row off, and enter numeric amounts.

ProductRegionSales
BinderNorth60
BinderSouth40
BinderNorth25
NotebookNorth30
NotebookSouth20
NotebookSouth15

With H4:K9 clear, enter this in H4:

=PIVOTBY(Sales[[#All],[Product]],Sales[[#All],[Region]],Sales[[#All],[Sales]],LAMBDA(x,SUM(x)),3,1,1,1,1)
Formula

The result fills H4:K9. Its first three rows contain the headers: I4 is Region, I5:K5 is North, South, Total, and H6:K6 is Product, Sales, Sales, Sales. The remaining rows are:

Binder      85     40    125
Notebook    30     35     65
Total      115     75    190
Example

Binder's North total is 60 + 25 = 85; its South total is 40. Notebook contributes 30 in North and 20 + 15 = 35 in South. The bottom-right grand total is 190.

LAMBDA(x,SUM(x)) adds the amounts for each group. After it, 3 displays the source headers. The four 1 settings request a grand-total row, ascending product order, a grand-total column, and ascending region order.

Return the same cross-tab without grand totals

Using the same six-row Sales Table, replace the formula in H4 with:

=PIVOTBY(Sales[[#All],[Product]],Sales[[#All],[Region]],Sales[[#All],[Sales]],LAMBDA(x,SUM(x)),3,0,1,0,1)
Formula

The expected numeric body has two rows and two columns, with Binder before Notebook and North before South:

Binder      85    40
Notebook    30    35
Example

Both total-depth arguments are now 0, so the grand-total row and column are omitted. The header setting remains 3, and the grouped amounts stay the same. See the sales summary walkthrough for the Table setup and an added-transaction check.

Common mistakes and notes

PIVOTBY doesn't create an interactive PivotTable

The output belongs to a formula and recalculates with it. It has no PivotTable field list and can't serve as the PivotTable reference for GETPIVOTDATA. Use the PivotTable workflow when you want that interface.

Keep headers and totals separate

The examples include source headers through #All and show them with header mode 3. Keep the source Table's Total Row off: #All would include that row too. Output grand totals are controlled separately by row_total_depth and col_total_depth.

Check the amounts as well as the resulting total

The SUM calculation ignores text and empty cells in its range input. A summary can look complete while an imported text amount contributes nothing. Align row labels, column labels, values, and any filter array to the same source rows.

Leave enough space and use a supported aggregation

Keep the formula outside the source Table. A new product needs another output row; a new region needs another output column. Blocking cells can prevent the result from spilling.

Microsoft documents bare SUM as an aggregation argument. It returned #NAME? on the tested Mac 16.114 installation, while the explicit LAMBDA form above worked. That observation is specific to the tested installation.

Related functions

Related articles

Deep dives, troubleshooting guides, and practical examples that use PIVOTBY.

Official documentation