The COUNT, COUNTA and COUNTBLANK functions in Excel
COUNT tells you how many cells in a range contain numbers. Its siblings cover the other cases: COUNTA counts every cell that isn't empty (numbers, text, dates, errors), and COUNTBLANK counts the empty ones.
Picking the right one matters: to count how many people are on a list, count the names with COUNTA; to count how many have a numeric score, use COUNT.
Syntax
=COUNT(value1, [value2], …)
| Argument | What it means |
|---|---|
| value1 | The first range or value to count, e.g. B2:B6. |
| value2, …optional | More ranges or values. Up to 255. |
Examples
1. How many numeric scores?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Scores | |
| 2 | Noa | 72 | 3 | |
| 3 | Liam | absent | ||
| 4 | Maya | 91 | ||
| 5 | Dan | |||
| 6 | Eli | 85 |
72, 91 and 85 are numbers. "absent" is text and B5 is empty, so neither counts.
2. Every non-empty cell: COUNTA
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Filled in | |
| 2 | Noa | 72 | 4 | |
| 3 | Liam | absent | ||
| 4 | Maya | 91 | ||
| 5 | Dan | |||
| 6 | Eli | 85 |
COUNTA also counts the text "absent": 4 of 5 cells are filled in.
3. Empty cells: COUNTBLANK
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Missing | |
| 2 | Noa | 72 | 1 | |
| 3 | Liam | absent | ||
| 4 | Maya | 91 | ||
| 5 | Dan | |||
| 6 | Eli | 85 |
Only Dan's cell is empty. Handy for spotting gaps in a list.
4. How many people are on the list?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Students | |
| 2 | Noa | 72 | 5 | |
| 3 | Liam | absent | ||
| 4 | Maya | 91 | ||
| 5 | Dan | |||
| 6 | Eli | 85 |
Names are text, so count them with COUNTA. =COUNT(A2:A6) would return 0.
Common mistakes
COUNT returns 0 on a list of names. Names are text, and COUNT only counts numbers.
Fix: Use COUNTA for text.
COUNTA counts cells that look empty. A space or a formula returning "" makes a cell non-empty.
Fix: Clean stray spaces, or count with a condition using COUNTIF.
Numbers stored as text aren't counted by COUNT.
Fix: Convert them to real numbers (Data › Text to Columns, or multiply by 1).
Frequently asked questions
How do I count cells that meet a condition?
Use COUNTIF, e.g. =COUNTIF(B2:B6,">80"), or COUNTIFS for several conditions.
Does COUNT count dates?
Yes. Excel stores dates as numbers, so COUNT includes them.
Related functions
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.
- COUNTIFSCOUNTIFS counts the rows that meet several conditions at once, such as a region and an amount range.
- AVERAGEAVERAGE returns the arithmetic mean of numbers or a range, skipping text and empty cells.