What is the syntax for PIVOTBY in Excel?

The syntax for PIVOTBY is: =PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array])

What are common errors with PIVOTBY?

#VALUE! if function is invalid. #CALC! if arrays are mismatched

How do I use the PIVOTBY function?

To use PIVOTBY, type =PIVOTBY( in a cell and provide the required arguments. Excel will show you the syntax and parameter hints as you type. Make sure to close the parentheses and press Enter to execute the function.

Why is PIVOTBY not working in my Excel?

Common issues with PIVOTBY include #VALUE! if function is invalid; #CALC! if arrays are mismatched. Start by checking the syntax =PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array]), then verify each referenced cell or range.

Lookup & Reference

PIVOTBY Function in Excel

Creates a pivot table-like summary with row and column groupings.

Syntax

=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array])

Arguments

row_fields (required): Columns for row grouping
col_fields (required): Columns for column grouping
values (required): Values to aggregate
function (required): Aggregation function
field_headers (optional): Header options
row_total_depth (optional): Row subtotal levels
row_sort_order (optional): Row sort order
col_total_depth (optional): Column subtotal levels
col_sort_order (optional): Column sort order
filter_array (optional): Boolean filter array

Examples

=PIVOTBY(A2:A100, B2:B100, C2:C100, SUM) - Pivot sum by two dimensions - Result: Pivot table

When to use PIVOTBY

Use PIVOTBY when you need to retrieve values from a table or range based on a key.
Confirm data types match (text vs number) and trim spaces before lookups.
Pair with [IFERROR](/functions/iferror/) or [IFNA](/functions/ifna/) for cleaner reports.
See the [lookup functions guide](/guides/excel-lookup-functions-guide/) for VLOOKUP, XLOOKUP, and INDEX MATCH paths.

Common PIVOTBY mistakes

#N/A often means the key is missing or exact match is wrong — see [#N/A error](/errors/na/).
Lock table references with $ when copying formulas down a column.
Compare legacy vs modern lookups in [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/).

Common errors

#VALUE! if function is invalid
#CALC! if arrays are mismatched

Use cases

Cross-tabulation
Sales by region and product
Multi-dimensional analysis

Frequently asked questions

What is the PIVOTBY function in Excel? PIVOTBY creates a pivot table-like summary with row and column groupings. The syntax is =PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array]). Typical use cases include cross-tabulation, sales by region and product, multi-dimensional analysis.
How do I use the PIVOTBY function? To use PIVOTBY, type =PIVOTBY( in a cell and provide the required arguments. Excel will show you the syntax and parameter hints as you type. Make sure to close the parentheses and press Enter to execute the function.
Why is PIVOTBY not working in my Excel? Common issues with PIVOTBY include #VALUE! if function is invalid; #CALC! if arrays are mismatched. Start by checking the syntax =PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array]), then verify each referenced cell or range.

Editorial review

Reviewed by Excel.Directory Editorial Team. Updated May 2026.