One row per record

That was not a rounding problem or a criteria typo — the formula found one matching row, because one row is all there is as far as the grid is concerned. COUNTIF says so in a single number.

This is the most dangerous failure shape in spreadsheet work: the formula is correct, the criteria is spelled correctly, no error appears, and the answer is a third of the truth. Every check you could run on the formula itself passes. The problem is in the shape of the data.

A9:C14 is the identical data with the city repeated on every row. Nothing about it is cleverer. It is just complete — every row now states all three of its fields without borrowing any of them from the row above.

Run the same formula against it.

That is the whole rule. The repetition looks redundant to a human reader and is exactly what makes the data computable. If you are handed a merged-and-blanked sheet, your first move — before any analysis — is to unmerge it and fill every key down.

A16:C19 is the third shape, and the one that looks most respectable: one row per city, one column per year. It prints beautifully. It is a report, not data.

Ask it for Penang in 2025 and the formula works — but notice where the year went.

The number 2025 does not appear anywhere in that formula. It is hidden inside the letter C.

That is the cost. A value that lives in a column heading instead of a cell cannot be filtered on, grouped by, compared against, or asked about. To answer "which city grew fastest?" you write arithmetic between column letters and redo it when 2026 arrives. On a one-row-per-record layout — city, year, orders — the year is just another field, and every one of those questions is one formula that never needs editing again.

The repair is to unpivot: turn each year column into rows. Three cities × two years becomes six rows. It always looks like a step backwards and it always is not.

The third break is the same idea one floor up: a stacked header, where the real column names sit on row 2 under a merged banner on row 1. Formulas that expect one header row read the banner instead. That one gets its own chapter next, because the header row is doing more work than it appears to be.

Before computing anything on a sheet somebody sent you, four questions:

• What is one row? If you cannot finish the sentence "each row is one ___", the layout is not settled yet and neither is the analysis.
• Are there merged cells? Unmerge, then fill every key down.
• Is any value living in a column heading? Dates, months, years, regions, product names — those are data pretending to be structure.
• Is there a total row inside the block? A Total row sitting among the data will be counted as a record by every formula that ranges over it, silently doubling the answer. Totals belong outside the data, and that separation is its own chapter later in this track.

None of this is analysis. All of it decides whether the analysis is possible.