Excel track · Exercise 17 of 86 Paid
COUNT: numbers only, and what it skips
Count the cells that hold real numbers, and read a short count as evidence of text.
Beginner
Readings with text and blanks
Read-only data · answer in B8
In B8, count how many readings in B2:B7 are really numbers.
| A | B | |
|---|---|---|
| 1 | Sample | Reading |
| 2 | S-01 | 12.5 |
| 3 | S-02 | n/a |
| 4 | S-03 | 8.2 |
| 5 | S-04 | |
| 6 | S-05 | 15 |
| 7 | S-06 | 7 |
| 8 | Readings counted |
Reference answer
What it does
COUNT answers one thing about a range: how many of its cells hold a number. Text, blanks and logical values are passed over without complaint. This column returns 3, from the 12.5 in B2, the 8.2 in B4 and the 15 in B6.
The cells that do not count are the ones worth a look. B3 holds the text n/a, B5 is empty, and B7 holds a 7 that Excel keeps as text: it looks like a number and is not one. SUM over the same range returns 35.7, so the two readings of the column already disagree.
A count that comes back short is the quickest signal that some figures arrived as text. Set the column to left align and the numbers will shift, leaving the text where it was. COUNT never reports those cells; it just leaves them out.
Syntax
=COUNT(value1, [value2], ...)Worked example
On Readings with text and blanks, =COUNT(B2:B7) returns 3.
=COUNT(B2:B7)Common errors
| Error | What it means and how to fix it |
|---|---|
One short | A reading stored as text is skipped, so the count lands below the figures you can see. |
Circular | A range that reaches B8 includes the cell holding the formula, so Excel reports a circular reference. |
#REF! | The range points at a row or a column that has since been deleted, leaving nothing to inspect. |
At work
Importing a supplier's stock file, where some quantities arrive as text. The count tells you how many rows to fix before the file is usable.