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). Column E is where the cleaned status goes. 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 PROPER(TRIM(...)) turn it into?
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | OrderID | Region | StatusRaw | Amount | Status | 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 |
How Excel works it out · run it to see each value
- 1TRIM(C2)=?spaces gone, still lowercase
- 2PROPER(TRIM(C2))=?one spelling for every row
Step 2: clean the whole column, then compute off it
One cleaned cell isn't a cleaned column. Write the same formula for O-1002, whose status arrived in capitals, then fill it through every row.
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.
Clean StatusRaw for every order
In E3, clean C3's status the same way, then fill the column: all five rows should read either Delivered or Cancelled.
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | OrderID | Region | StatusRaw | Amount | Status | 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 |
Step 3: roll it up by region, off the cleaned column
Column E arrives already cleaned this time. 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. Then fill down for South.
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | OrderID | Region | StatusRaw | Amount | Status | Region | Delivered Total |
| 2 | O-1001 | North | delivered | 250 | =PROPER(TRIM(C2)) | North | |
| 3 | O-1002 | South | CANCELLED | 100 | =PROPER(TRIM(C3)) | South | |
| 4 | O-1003 | North | delivered | 400 | =PROPER(TRIM(C4)) | ||
| 5 | O-1004 | South | Delivered | 150 | =PROPER(TRIM(C5)) | ||
| 6 | O-1005 | North | cancelled | 300 | =PROPER(TRIM(C6)) |
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.