Learning Excel: COUNT
Counting what's there
"How many plots are actually rented out?" Ruth counts on her fingers and gets a different answer every time. Excel counts better, as long as you pick the right function.
Counting numbers
COUNT counts how many cells in a range contain a number. It doesn't add them up, it counts them.
=COUNT(D2:D11) gives 8: eight plots have a tomato harvest. The empty cells don't count.
=COUNT(D2:D11)
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) | |||
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 | 8.00 | ||
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | ||||
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 | |||
| 5 | P04 | 25 | |||||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 | |||
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | ||||
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 | |||
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 | |||
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | ||||
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 | |||
| 12 | Average | ||||||||
| 13 | Highest | ||||||||
| 14 |
Counting everything, or what's empty
COUNT only counts numbers. Want to count text as well? Then use COUNTA: it counts every cell that isn't empty.
COUNTBLANK does the opposite: it counts the empty cells. =COUNTA(B2:B11) gives 9 tenants, because P04 is vacant.
=COUNTA(B2:B11)
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) | |||
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 | 9.00 | ||
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | ||||
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 | |||
| 5 | P04 | 25 | |||||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 | |||
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | ||||
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 | |||
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 | |||
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | ||||
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 | |||
| 12 | Average | ||||||||
| 13 | Highest | ||||||||
| 14 |
Try it yourself
How many plots are rented out? Count the tenants in I2.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Plot | Tenant | m² | Tomatoes (kg) | Courgettes (kg) | Beans (kg) | ||||
| 2 | P01 | Molly | 50 | 18.40 | 9.20 | 4.50 | ||||
| 3 | P02 | Karim | 25 | 7.90 | 2.10 | |||||
| 4 | P03 | Annabel | 50 | 21.60 | 12.80 | 6.30 | ||||
| 5 | P04 | 25 | ||||||||
| 6 | P05 | Will | 75 | 30.20 | 15.50 | 8.80 | ||||
| 7 | P06 | Sophie | 25 | 6.50 | 4.10 | |||||
| 8 | P07 | Ben | 50 | 15.70 | 10.30 | 5.20 | ||||
| 9 | P08 | Fatima | 75 | 27.90 | 18.60 | 9.40 | ||||
| 10 | P09 | Luke | 25 | 3.80 | 1.90 | |||||
| 11 | P10 | Jodie | 50 | 19.30 | 11.10 | 5.70 | ||||
| 12 | Average | |||||||||
| 13 | Highest | |||||||||
| 14 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson