Count the stations with no reading (blank, not zero)
The problem
Problem statement
Two stations in A2:B7 have not reported a reading yet, and two others genuinely measured zero. The operations report needs the number of *missing* readings in the No reading cell D2.
A measured 0 is data; only truly empty cells count as missing.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | |
|---|---|---|---|
| 1 | Station | Reading | No reading |
| 2 | S-01 | 12 | |
| 3 | S-02 | 0 | |
| 4 | S-03 | ||
| 5 | S-04 | 8 | |
| 6 | S-05 | 0 | |
| 7 | S-06 |
Notes
COUNTIF(range, "") counts empty cells and ignores zeros. COUNTBLANK is the dedicated function and returns the same number. COUNTIF(range, 0) would count the zeros instead — the two are one character apart and routinely confused, which is why a report can say "2 missing" while four cells look empty-ish.
How to approach
Your answer goes in D2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is D2
- Look at B3 and B6 — those are zeros, not blanks
- COUNTIF can match an empty string
- Shape: =COUNTIF(?, ?)
Official solutions
Show 2 solutions — try it yourself first
Approach 1: COUNTIF against an empty string
=COUNTIF($B$2:$B$7, "")
Only B4 and B7 are empty, so the answer is 2. The two zeros in B3 and B6 are stored values and do not match an empty string.
Approach 2: COUNTBLANK
=COUNTBLANK($B$2:$B$7)
Same count with a function whose whole job is this. It also counts cells containing "" returned by a formula, which COUNTIF does differently in some versions — one reason COUNTBLANK is the safer report tool.
Function guide
FAQ
How do I count empty cells in a range?
=COUNTIF(range, "") or =COUNTBLANK(range). Both ignore cells containing a number, including zero.
Why does COUNTIF(range, 0) give a different number?
It counts cells equal to zero, which is the opposite question. Missing readings are blanks; zero readings are data.