COUNT and COUNTA in Excel: how to use it
COUNT counts cells that contain a number. COUNTA counts every cell that isn't empty, text included.
Start practising hereIn the lesson COUNT and COUNTA, free
What do COUNT and COUNTA do?
COUNT counts how many cells in a range contain a number. Dates count too, because to Excel a date is a number. COUNTA counts all the cells that aren't empty, so text as well.
Both functions count cells: they don't add anything up. =COUNT(C2:C6) gives 5 if there are five amounts, however large those amounts are. If you want to know how many cells are empty, use COUNTBLANK.
When do you use COUNT and COUNTA?
- How many orders have an amount: =COUNT(C2:C100).
- How many names are on a sign-up list: =COUNTA(A2:A100).
- How many students haven't taken a test yet: =COUNTBLANK(D2:D30).
- Checking an average yourself: =SUM(C2:C6)/COUNT(C2:C6).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
How many cells contain a number. Dates count too.
Filled in: =COUNT(B2:B5)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
COUNT | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | COUNT |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
value1 | A cell or a range you want to count. | B2:B5 |
[value2] | Optional: more cells or ranges, separated by commas. | left out |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. | |
... | You can give more, with a comma between each one. |
COUNT and COUNTA step by step
E1 contains =COUNTA(A2:A6). Counts the regions, which are text. COUNT would give 0 here. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click the cell where the count should go, here E1. You're counting regions, and that's text, so you choose COUNTA.
=COUNTA( - Select the cells you want to count, from A2 to A6.
=COUNTA(A2:A6 - Close the bracket and press Enter: there are 5 regions in the list.
=COUNTA(A2:A6)
More examples
Counting only numbers
D1 contains =COUNT(B2:B5). COUNT only counts the two scores. The text absent and the empty cell don't count.
Everything that's filled in
D1 contains =COUNTA(B2:B5). COUNTA counts absent as well, because text counts as filled in too. Only Noah's empty cell doesn't count.
Counting empty cells
D1 contains =COUNTBLANK(B2:B5). One student doesn't have a score yet.
Which one do you need?
If you're counting amounts or quantities, use COUNT. If you're counting names or codes, use COUNTA. Empty cells are counted with COUNTBLANK.
Where it often goes wrong
- A cell with just a space looks empty, but COUNTA counts it.
- COUNT gives 0 if your numbers are stored as text.
Practise this in the lesson COUNT and COUNTA. It's free.