=COUNT(B2:B8) counts the cells in B2:B8 that contain numbers. =COUNTA(B2:B8) counts every cell that is not empty, whatever it holds. =COUNTBLANK(B2:B8) counts the empty cells.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Score | COUNT | COUNTA | COUNTBLANK | ||
| 2 | Ana | 78 | 4 | 6 | 1 | ||
| 3 | Ben | absent | |||||
| 4 | Chen | 91 | |||||
| 5 | Dina | ||||||
| 6 | Eli | 65 | |||||
| 7 | Fay | pending | |||||
| 8 | Gus | 88 |
COUNT gives 4: the four scores. COUNTA gives 6, because "absent" and "pending" are not empty either. COUNTBLANK gives 1, for Dina. Type a score for Dina and COUNT and COUNTA both go up while COUNTBLANK drops to 0. COUNTA plus COUNTBLANK equals the 7 cells of the range, unless a cell holds a formula returning "", as a section below shows.
COUNT: count cells with numbers
=COUNT(value1, [value2], ...)
COUNT counts numbers. Dates and times are numbers in Excel, so they count too. Text, TRUE/FALSE, errors and empty cells are skipped. Use it to count how many entries are real values, for example how many tests were marked.
Why COUNT skips numbers stored as text
A number typed with an apostrophe, or pasted from another program, can be stored as text. It looks like a number but COUNT ignores it, and so do SUM and AVERAGE.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Amount | COUNT | COUNTA | |
| 2 | 1001 | 250 | 3 | 5 | |
| 3 | 1002 | 400 | |||
| 4 | 1003 | 175 | |||
| 5 | 1004 | 2026-03-15 | |||
| 6 | 1005 | 90 |
COUNT gives 3: 250, 175 and the date. The two amounts in B3 and B6 are text, so only COUNTA, with 5, includes them. When COUNT gives fewer than you expect, compare it with COUNTA: the difference is the number of cells holding text (or TRUE/FALSE, or an error). Converting text to numbers fixes the cells.
COUNTA: count cells that are not empty
=COUNTA(value1, [value2], ...)
COUNTA is the function for "how many rows have an entry". Point it at a column that is always filled, such as names, to count the rows of a list:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Reply | Invited | Replied | ||
| 2 | Ana | ana@mail.com | Yes | 6 | 4 | |
| 3 | Ben | ben@mail.com | ||||
| 4 | Chen | chen@mail.com | No | |||
| 5 | Dina | dina@mail.com | Yes | |||
| 6 | Eli | eli@mail.com | ||||
| 7 | Fay | fay@mail.com | Maybe |
Six people were invited and four replied. COUNTA counts "Yes", "No" and "Maybe" alike. To count only the "Yes" replies, use a condition: =COUNTIF(C2:C7,"Yes") (COUNTIF).
COUNTA counts formulas that return ""
COUNTA looks at whether a cell has content, not at what it shows. A formula such as =IF(B2>=70,"Yes","") returns an empty string when the score is under 70: the cell looks empty, but it holds a formula, so COUNTA counts it. COUNTBLANK counts it as blank too, which is the one case where a cell is counted by both.
With the scores 78, 52, 91, 64 and 85 in B2:B6 and that formula filled down C2:C6, Excel returns:
=COUNTA(C2:C6) 5 (every cell holds a formula)
=COUNTBLANK(C2:C6) 2 (the two "" results)
=COUNTIF(C2:C6,"Yes") 3 (only the visible results)
To count only the cells that show something, count the value you want, as COUNTIF does here, or use =SUMPRODUCT(--(C2:C6<>"")), which also gives 3. The COUNTIF not blank page has more ways to do it.
Practice: count the marked tests
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Score | Scored | |||
| 2 | Ana | 78 | ||||
| 3 | Ben | absent | ||||
| 4 | Chen | 91 | ||||
| 5 | Dina | 85 | ||||
| 6 | Eli | absent | ||||
| 7 | Fay | absent | ||||
| 8 | Gus | 59 |
Your turn: In F2, count how many students have a score. Students who missed the test are marked "absent" and must not be counted.
Common mistake: COUNT on a text column
=COUNT(A2:A8) on a column of names returns 0, because COUNT only counts numbers. That surprises people who expect it to count rows. For names, codes, phone numbers or anything else that is text, use COUNTA. And when the status bar in Excel shows "Count" for a selection, that is COUNTA; "Numerical Count", which you can switch on by right-clicking the status bar, is COUNT.
Frequently Asked Questions
What is the difference between COUNT and COUNTA in Excel?
COUNT counts only cells that contain numbers, including dates and times. COUNTA counts every cell that is not empty: numbers, text, TRUE/FALSE, errors and formulas, even a formula that returns an empty string.
Why does COUNT return 0 in Excel?
The cells hold text, not numbers. That includes numbers stored as text, which often show a green triangle. Use =COUNTA(B2:B8) to count them anyway, or convert them to numbers first.
How do I count cells that are not empty in Excel?
=COUNTA(B2:B8) counts every non-empty cell. If some cells hold formulas that return "" and you want to skip those too, use =COUNTIF(B2:B8,"?*") for text or =SUMPRODUCT(--(B2:B8<>"")) for anything.
How do I count blank cells in Excel?
=COUNTBLANK(B2:B8). It counts truly empty cells and cells whose formula returns an empty string "". A cell with a space in it is not blank.
