Statistical Functions
Beginner
COUNT over a block
One range can cover a whole grid of readings.
Task:
Five sensors log a reading each hour, and dropped readings are left blank. In B9 give the total number of readings received across the whole grid.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of COUNT, COUNTA
0/3The same formula in the other shapes it takes at work.
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sensor | 09:00 | 10:00 | 11:00 | 12:00 |
| 2 | S1 | 21.4 | 21.9 | 22.6 | |
| 3 | S2 | 19.8 | 20.4 | ||
| 4 | S3 | 23.1 | 23 | 23.4 | 23.9 |
| 5 | S4 | 18.7 | 18.9 | ||
| 6 | S5 | 22 | 22.2 | 22.1 | 22.5 |
| 7 | |||||
| 8 | |||||
| 9 | Readings received |
What this exercise teachesMay contain the answer
Twenty slots and fewer readings: the gap is your data-loss rate. Counting the block in one go is simpler and safer than adding five row counts, and it keeps working if a sixth sensor is inserted inside the range.