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

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

  1. Sales by region and rep

    ABCDEFGHI
    1RegionRepSalesAnaBenChloeTotal
    2NorthAna1200North15009002400
    3SouthBen800South800800
    4NorthChloe900West400400
    5WestAna400Total19008009003600
    6NorthAna300

    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

    Try this example

Common mistakes

Try PIVOTBY in the playground

Practise it

Related functions