Level 7 · Boss: the channel × status summary table

One formula, nine cells

A summary table is a grid of conditional totals: one condition from the row label, one from the column label. You could write nine different SUMIFS, but then a change means nine edits, and one typo hides in plain sight. The analyst’s way is one formula, written so it can be dragged across and down.

The sheet holds all 51 orders of Saturday, November 29, in A1:D52. The table’s labels are already there: channels down F5:F7, statuses across G4:I4, totals in column J and row 8.

Step 1: one cell, with the right $ signs

The first cell is completed app revenue: channel from F5, status from G4.

G5   =SUMIFS($D$2:$D$52,$B$2:$B$52,$F5,$C$2:$C$52,G$4)

Each reference gets exactly the lock it needs:

Reference	Lock	Why
$D$2:$D$52, $B$2:$B$52, $C$2:$C$52	column and row	the data never moves
$F5	column only	every cell reads column F, but its own row
G$4	row only	every cell reads row 4, but its own column

The mixed references from chapter 1 ($F5, G$4) are what make the grid work. Read them as “always look left to column F” and “always look up to row 4”.

Step 2: drag it across and down

Fill G5 right to I5, then fill G5:I5 down to row 7. Check two cells in the formula bar:

I5   =SUMIFS($D$2:$D$52,$B$2:$B$52,$F5,$C$2:$C$52,I$4)
G7   =SUMIFS($D$2:$D$52,$B$2:$B$52,$F7,$C$2:$C$52,G$4)

I5 is refunded app revenue: 0. G7 is completed web revenue: 4,991.16. The zeros in the table are real: on this day the only refunded order came through the web.

What goes wrong with the other locks:

• No $ on the labels (F5, G4): filled right, F5 becomes G5, a number inside the table, so H5 looks for a channel called 8757.22 and shows 0.
• Full lock on the labels ($F$5, $G$4): every cell repeats the app/completed number.
• No $ on the data: the ranges slide down and right with the formula and miss rows.

Step 3: totals and the check

J5:J7   =SUM(G5:I5)
G8:J8   =SUM(G5:G7)
G10     =SUM(D2:D52)-J8

J8 is the grand total, 21,175.70. G10 compares it with the plain sum of the data. Zero means every order was counted once: no channel or status is missing from the labels, and none was double counted.

Thinking like an analyst

The table answers Jordan’s question at a glance. Completed revenue was 19,382.02. Cancelled orders were 1,596.70, almost all from the app (1,376.26). Refunds were small: one web order of 196.98. Before the meeting, the check cell is what you point to when someone asks “does this include everything?”

A check cell is cheap insurance on every summary table. Data changes, labels get typos, and a new status shows up in next month’s export. A SUMIFS on a label that matches no row just shows 0, so without the check, a missing slice is invisible.

Common mistakes

• Writing a separate formula per cell instead of one dragged formula.
• Locking F5 and G4 fully, so every cell shows 8,757.22.
• Taking the totals from the data (=SUM(D2:D52)) in J8 instead of from the table: the check then compares the data with itself and always says 0.
• Typing the status into the formula ("completed"), which breaks the drag across.