Chapter 01 · 7 min

Somebody sends you a spreadsheet

A colleague forwards you a file. Can you get me the Penang total by lunch? No column list, no notes, no warning about what is in it.

Everything an analyst does happens between that message and your reply. This chapter is the whole of it, on ten rows you can count by eye — see what arrived, check whether the columns are what they claim, find what is broken, answer the question, and say plainly what your number does and does not include.

Every later chapter in this track is one of these steps, slowed down.

The grid

Ten orders in A1:C11, exactly as they were sent — order_id, city, amount_myr. Nothing has been tidied. Work in column E and leave the raw block alone.

First: how much is there

Before anything clever, find out what you are holding. COUNTA counts filled cells, so counting the order_id column tells you how many orders were sent.

It sounds too simple to bother with. It is the single most useful thing you can do first, because every number you produce afterwards is a claim about these rows, and you want to have said out loud how many of them there are. If somebody later says that looks low, the first question is always whether you got the whole file.

Look at the grid and count the order rows. Then run it and see whether the sheet agrees with you.

Scroll to see all 5 columns →

	A	B	C	E
1	order_id	city	amount_myr
2	1001	Kuala Lumpur	62.50	=COUNTA(A2:A11)
3	1002	Penang	41.00
4	1003	Kuala Lumpur	128.75
5	1004	Johor Bahru	33.20
6	1005	Penang	RM 48.00
7	1006	Kuala Lumpur	96.40
8	1007	Ipoh	27.00
9	1008	Penang	54.30
10	1009	Johor Bahru
11	1010	Kuala Lumpur	71.15

Editable, try changing it

Does the money column contain money

Ten orders arrived. Now ask whether the amount_myr column really holds ten amounts.

COUNT counts numbers only, and it does not complain about anything else — it just skips it. So COUNT on a column that is supposed to be money is a direct measure of how much of it actually is. Ten rows in, and whatever comes back, the gap is the part of the column you cannot add up.

Ten orders were sent. How many of the amounts does Excel consider a number?

Scroll to see all 5 columns →

	A	B	C	E
1	order_id	city	amount_myr
2	1001	Kuala Lumpur	62.50
3	1002	Penang	41.00	=COUNT(C2:C11)
4	1003	Kuala Lumpur	128.75
5	1004	Johor Bahru	33.20
6	1005	Penang	RM 48.00
7	1006	Kuala Lumpur	96.40
8	1007	Ipoh	27.00
9	1008	Penang	54.30
10	1009	Johor Bahru
11	1010	Kuala Lumpur	71.15

Editable, try changing it

The total, and the total that is right

Two of those ten rows are not numbers: C10 is empty, and C6 reads RM 48.00 — a currency symbol typed into the cell, which makes the whole thing text.

SUM skips both without a word. No error, no warning, no coloured triangle. Add the visible amounts by hand and you get 562.30; run SUM and you will get less than that, and nothing on the screen tells you so.

This is the shape of almost every real spreadsheet bug. Not a formula that breaks — a formula that quietly answers a slightly different question than the one you asked.

By hand the amounts come to 562.30. Run it and see what the sheet says instead.

Scroll to see all 5 columns →

	A	B	C	E
1	order_id	city	amount_myr
2	1001	Kuala Lumpur	62.50
3	1002	Penang	41.00
4	1003	Kuala Lumpur	128.75	=SUM(C2:C11)
5	1004	Johor Bahru	33.20
6	1005	Penang	RM 48.00
7	1006	Kuala Lumpur	96.40
8	1007	Ipoh	27.00
9	1008	Penang	54.30
10	1009	Johor Bahru
11	1010	Kuala Lumpur	71.15

Editable, try changing it

Now the question they actually asked

The request was the Penang total, not the grand total. SUMIFS adds only the rows whose city matches — the range to add, then the range to test, then what to test it against.

Write it yourself this time.

Penang total

In E5, total only the Penang orders: the amount range first, then the city range, then the city you want.

Scroll to see all 5 columns →

	A	B	C
1	order_id	city	amount_myr
2	1001	Kuala Lumpur	62.50
3	1002	Penang	41.00
4	1003	Kuala Lumpur	128.75
5	1004	Johor Bahru	33.20
6	1005	Penang	RM 48.00
7	1006	Kuala Lumpur	96.40
8	1007	Ipoh	27.00
9	1008	Penang	54.30
10	1009	Johor Bahru
11	1010	Kuala Lumpur	71.15

Two Penang orders, or three

You have a Penang total. Before sending it, check it is built from the rows you think it is — count them.

COUNTIF says how many orders are Penang's. The total you just produced is built from fewer than that, because one of the Penang rows is the RM 48.00 cell and SUMIFS skipped it exactly the way SUM did. The answer is RM 48.00 short, and it looks completely reasonable.

Nobody catches this by staring at the number. You catch it by counting the rows twice: once as they exist, once as they were used.

How many rows in this file are Penang orders? Compare that against how many the total above could possibly have used.

Scroll to see all 5 columns →

	A	B	C	E
1	order_id	city	amount_myr
2	1001	Kuala Lumpur	62.50
3	1002	Penang	41.00
4	1003	Kuala Lumpur	128.75
5	1004	Johor Bahru	33.20
6	1005	Penang	RM 48.00	=COUNTIF(B2:B11,"Penang")
7	1006	Kuala Lumpur	96.40
8	1007	Ipoh	27.00
9	1008	Penang	54.30
10	1009	Johor Bahru
11	1010	Kuala Lumpur	71.15

Editable, try changing it

What you send back

So the reply is not a number. It is a number with a sentence attached:

Penang came to RM 95.30 across two orders. A third Penang order arrived as text rather than a number (RM 48.00) and is not included — with it the figure is RM 143.30. Say which you want and I will lock it in.

That is the whole job. It took five formulas, none of them harder than SUM, and the value was never in the formulas — it was in checking the count, noticing the gap, and writing down what the number excludes instead of hoping nobody asks.

The repair itself is the smaller half, and the free Spreadsheet Fundamentals chapter on column types covers it.

The rest of this track is these same steps, one at a time and properly: pinning down what was asked before you compute, looking over a sheet you have never seen, counting things correctly, and what to do when one city is spelled four different ways.