Clean a messy status column, then roll it up by branch
Column A holds an Mr DIY branch, column B a delivery status typed inconsistently (mixed case, stray spaces), column C an amount. First, in E2:E6, write a formula that normalizes each status to a clean "Delivered" or "Cancelled". Then, in G2:G3, write a rollup that sums Amount per branch (branches listed in F2:F3) for delivered orders only — matching against your cleaned column, not the raw one.
Starting grid
Scroll to see all 7 columns →
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Branch | StatusRaw | Amount | Branch | Delivered Total | ||
| 2 | KL | delivered | 200 | KL | |||
| 3 | JB | CANCELLED | 90 | JB | |||
| 4 | KL | Delivered | 350 | ||||
| 5 | JB | delivered | 120 | ||||
| 6 | KL | cancelled | 80 |
Basic
The expected output and walkthrough for this question unlock with a Basic plan.
Basic
The reference formulas and walkthrough for this question unlock with a Basic plan.
Basic
This is a Basic question
Premium practice questions unlock with a Basic plan. The full solution, expected output, and grading are available to Basic members.
RM 25/mo · cancel anytime