Chapter 15 · advanced · 7 min
Capstone: clean it, then roll it up
Every real spreadsheet task looks like this: a raw data tab nobody cleaned before handing it to you, and a summary tab someone actually reads. This chapter builds both, in order — clean the messy column first, then roll it up by category. Nothing here is a new function; it's the same TRIM/PROPER from the cleaning chapter and the same SUMIFS from the conditional-aggregation chapter, chained the way a real task actually chains them.
Think of columns A-D as the "Transactions" tab and columns F-G as the "Summary" tab a manager would actually open — this playground keeps them on one grid, but the two-area split is the same one a two-tab workbook would use.
The grid
A raw orders table in A1:D6 (OrderID, Region, a messy Status column with inconsistent case and stray spaces, Amount). A summary area starts at F1.
Step 1: the raw column can't be trusted as-is
StatusRaw has three different spellings of the same two states — " delivered " with stray spaces, "CANCELLED" in caps, "delivered" typed clean. A SUMIFS matching against StatusRaw directly with the criteria "Delivered" would silently miss every row that isn't spelled exactly that way, undercounting with no error to flag it.
=PROPER(TRIM(C2))This is the same TRIM+PROPER pairing from the cleaning chapter — normalize before you aggregate, not after.
C2 is " delivered " — extra spaces, all lowercase. What does TRIM(PROPER(...)) turn it into?
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | OrderID | Region | StatusRaw | Amount | Region | Delivered Total | |
| 2 | O-1001 | North | delivered | 250 | =PROPER(TRIM(C2)) | North | |
| 3 | O-1002 | South | CANCELLED | 100 | South | ||
| 4 | O-1003 | North | delivered | 400 | |||
| 5 | O-1004 | South | Delivered | 150 | |||
| 6 | O-1005 | North | cancelled | 300 |
Step 2: the cleaned column becomes the real join key
Once every row's status is normalized the same way, the cleaned column — not the raw one — is what any later formula should reference. This is the habit that matters more than any single function: raw input columns are for record-keeping, cleaned columns are for computing.
C3 is "CANCELLED" — all caps, no stray spaces. What does the same formula return here?
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | OrderID | Region | StatusRaw | Amount | Region | Delivered Total | |
| 2 | O-1001 | North | delivered | 250 | North | ||
| 3 | O-1002 | South | CANCELLED | 100 | =PROPER(TRIM(C3)) | South | |
| 4 | O-1003 | North | delivered | 400 | |||
| 5 | O-1004 | South | Delivered | 150 | |||
| 6 | O-1005 | North | cancelled | 300 |
Step 3: roll it up by region, off the cleaned column
Now the summary tab's job is one SUMIFS per region, matching Region AND the cleaned status:
=SUMIFS($D$2:$D$6,$B$2:$B$6,F2,$E$2:$E$6,"Delivered")This is the SUMIFS chapter's core function and the cross-tab chapter's row-per-category pattern, aimed at the column the cleaning chapter produced — three chapters' worth of technique, one formula.
North's delivered total
In G2, write the SUMIFS that rolls up North's delivered total off the cleaned status column, not the raw one.
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | OrderID | Region | StatusRaw | Amount | Region | Delivered Total | |
| 2 | O-1001 | North | delivered | 250 | North | ||
| 3 | O-1002 | South | CANCELLED | 100 | South | ||
| 4 | O-1003 | North | delivered | 400 | |||
| 5 | O-1004 | South | Delivered | 150 | |||
| 6 | O-1005 | North | cancelled | 300 |
The habit, not the formula
Nothing in this chapter is new syntax — it's the order of operations that makes a rollup trustworthy: clean first, aggregate second, never the other way around. A SUMIFS pointed straight at a messy raw column will run without error and return a number that looks plausible, which is exactly what makes an unclean rollup dangerous — it fails silently, and the only way to catch it is to have checked the raw column first.