Excel functions / Dynamic arrays
PIVOTBY function
Summarises values by row fields down the side and a column field across the top, with totals: a PivotTable by formula that updates live. Spills.
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: What goes down the side: one or more columns.col_fields: What goes across the top: one column.values: The values to aggregate: one column, the same height.function: How to aggregate, by name: SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, MEDIAN or PERCENTOF.field_headers(optional): 0 no headers, 1 headers not shown, 3 headers shown.row_total_depth(optional): 0 no total row, 1 at the bottom (default), -1 at the top.row_sort_order(optional): 1 ascending (default), -1 descending.col_total_depth(optional): 0 no total column, 1 at the right (default), -1 at the left.col_sort_order(optional): 1 ascending (default), -1 descending.filter_array(optional): TRUE/FALSE for each row: only TRUE rows are used.
When to use it
PIVOTBY summarises a table by two fields at once, one down the side and one across the top, like a PivotTable written as a formula. It spills the whole table with totals.
Examples
Sales by region and rep
A B C D E F G H I 1 Region Rep Sales Ana Ben Chloe Total 2 North Ana 1200 North 1500 900 2400 3 South Ben 800 South 800 800 4 North Chloe 900 West 400 400 5 West Ana 400 Total 1900 800 900 3600 6 North Ana 300 In E1:
=PIVOTBY(A2:A6, B2:B6, C2:C6, SUM)→ , Ana, Ben, Chloe, Total; North, 1500, , 900, 2400; South, , 800, , 800; West, 400, , , 400; Total, 1900, 800, 900, 3600
Common mistakes
- Older versions of Excel. PIVOTBY is in Excel for Microsoft 365 only.
Practise it
Related functions
- GROUPBY: Groups rows by one or more fields and aggregates values for each group, with a total row: a pivot by formula that updates live. Spills.
- SUMIFS: Adds the cells that meet every one of several conditions.
- PERCENTOF: What share one set of values is of the whole: SUM(data_subset) ÷ SUM(data_all). Also works as the function in GROUPBY and PIVOTBY.