Chapter 10 · 8 min · Basic
A summary someone can use
Everything so far has been about getting the data honest. This is the part where somebody else reads it.
And the thing people actually receive is rarely the file. It is a small block of numbers, with labels, that somebody will paste into a message or read off a screen in a meeting — which means its job is not to be complete, it is to be understandable by a person who was not in the room while you worked.
Four things make that possible: one row per thing asked about, numbers rounded once at the end, the exclusions written down, and a line that proves the block adds up. None of them are hard. Skipping any of them is what turns good work into a number nobody trusts.
The grid
Ten orders in A1:D11 — order_id, branch, status, amount_myr. Three of them are cancelled. The summary block is started for you in F1:I7: the headers, the three branch names, and two labelled rows below them for the exclusions and the total. Fill in the numbers beside them.
One row per thing you were asked about
The question was how did each branch do, so the block gets one row per branch and nothing else. Three branches, three rows — already written into F2:F4.
Start with the count, because a money figure with no count behind it is unreadable: RM 200 could be one order or forty, and those are different businesses.
COUNTIFS takes both conditions the question implies — this branch, and completed. Notice it points at F2 rather than spelling out the branch name, so the same formula can be filled down the block and each row asks about its own label.
Penang has four orders in the file, and one of them is cancelled. How many will this count?
Scroll to see all 9 columns →
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | order_id | branch | status | amount_myr | branch | orders | revenue_myr | avg_order_myr | |
| 2 | 8001 | Penang | completed | 41.00 | Penang | =COUNTIFS($B$2:$B$11,F2,$C$2:$C$11,"completed") | |||
| 3 | 8002 | Kuala Lumpur | completed | 62.50 | Kuala Lumpur | ||||
| 4 | 8003 | Penang | cancelled | 128.75 | Johor Bahru | ||||
| 5 | 8004 | Johor Bahru | completed | 33.20 | |||||
| 6 | 8005 | Kuala Lumpur | completed | 71.15 | excluded: cancelled | ||||
| 7 | 8006 | Penang | completed | 96.40 | completed, all branches | ||||
| 8 | 8007 | Johor Bahru | cancelled | 27.00 | |||||
| 9 | 8008 | Kuala Lumpur | completed | 48.00 | |||||
| 10 | 8009 | Penang | completed | 19.90 | |||||
| 11 | 8010 | Kuala Lumpur | cancelled | 55.00 |
Basic
The rest of this chapter is Basic
The Excel tutorial and Spreadsheet Fundamentals are free here, forever. This track is the paid half: what to actually do with a spreadsheet once somebody sends you one, from the first look to a summary you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Round once, at the end
- 03The average is a second decision
- 04Write the exclusions into the block
- 05The line that proves the block
- 06What you send, and what you keep