Chapter 13 · advanced · 6 min
Cross-tabs: building a pivot table out of formulas
A pivot table looks like a different tool from everything else in this curriculum, but it isn't — it's SUMIFS with two criteria instead of one, arranged on a grid. Building the cross-tab yourself, formula by formula, means you can reproduce a pivot's result anywhere a real pivot object isn't available (a locked template, a Google Sheets import, an interview whiteboard), and it makes the ribbon version genuinely easier to reason about afterward.
We'll cross-tabulate a small sales log by region and product.
The grid
A raw sales log in A1:C7 — Region, Product, Amount, one row per transaction. Two regions (North, South), two products (Snacks, Drinks).
UNIQUE builds the row labels for you
A cross-tab needs one row per distinct region. Rather than typing "North" and "South" by hand — which silently goes stale the moment a third region appears in the log — UNIQUE reads them straight off the data.
=UNIQUE(A2:A7)This spills down as many cells as there are distinct regions, in order of first appearance.
The log has transactions for North and South, North appearing first (row 2). How many cells will this spill into, and in what order?
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Amount | Snacks | Drinks | ||
| 2 | North | Snacks | 120 | =UNIQUE(A2:A7) | |||
| 3 | North | Drinks | 80 | ||||
| 4 | South | Snacks | 150 | ||||
| 5 | South | Drinks | 60 | ||||
| 6 | North | Snacks | 40 | ||||
| 7 | South | Drinks | 30 |
SUMIFS with a row criterion and a column criterion
This is the formula a pivot table computes internally for every cell in its body: sum Amount where Region matches this row's label AND Product matches this column's label.
=SUMIFS($C$2:$C$7,$A$2:$A$7,$D2,$B$2:$B$7,E$1)The $ placement is deliberate and different in each reference: $D2 locks the column so it always reads the row label as the formula is dragged right, but lets the row float; E$1 locks the row so it always reads the column label as the formula is dragged down, but lets the column float. Get either one backward and the cross-tab reads the wrong label the moment you copy it off the first cell.
North's Snacks rows are 120 and 40. What should this cell return?
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Amount | Snacks | Drinks | ||
| 2 | North | Snacks | 120 | =SUMIFS($C$2:$C$7,$A$2:$A$7,$D2,$B$2:$B$7,E$1) | |||
| 3 | North | Drinks | 80 | ||||
| 4 | South | Snacks | 150 | ||||
| 5 | South | Drinks | 60 | ||||
| 6 | North | Snacks | 40 | ||||
| 7 | South | Drinks | 30 |
Row subtotals with plain SUM
Once the body is filled in, a row subtotal is just SUM across that row's cross-tab cells — no new logic needed, because the hard part (matching two criteria at once) already happened in the body.
=SUM(E2:F2)North's row total
In G2, write a plain SUM across North's cross-tab body (E2:F2) for the row total.
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Amount | Snacks | Drinks | ||
| 2 | North | Snacks | 120 | ||||
| 3 | North | Drinks | 80 | ||||
| 4 | South | Snacks | 150 | ||||
| 5 | South | Drinks | 60 | ||||
| 6 | North | Snacks | 40 | ||||
| 7 | South | Drinks | 30 |
This is what the ribbon PivotTable does for you
Insert > PivotTable, then dragging Region to Rows, Product to Columns, and Amount to Values, produces exactly this grid — the ribbon tool is running the same SUMIFS logic and handling the UNIQUE-ing of labels and the subtotals automatically, rather than doing anything conceptually different. The formula version is worth knowing because it works anywhere a pivot object doesn't (a cell range you need to reference in another formula, a template that can't hold a pivot, explaining out loud what a pivot actually computes), and because a broken pivot table is far easier to debug once you know what it's supposed to be computing underneath.