How to / Counting
How to count cells with text in Excel
Use =COUNTIF(A2:A7, "*"). The asterisk stands for any text, so cells with numbers, dates or nothing in them are not counted.
Count the cells that hold text
| A | B | C | |
|---|---|---|---|
| 1 | Entry | ||
| 2 | Ana | 3 | |
| 3 | 42 | ||
| 4 | |||
| 5 | Ben | ||
| 6 | 2026-03-14 | ||
| 7 | n/a |
In C2: =COUNTIF(A2:A7, "*") → 3
Ana, Ben and n/a are text. 42 is a number, the date is a number too, and A4 is empty.
How it works
- COUNTIF counts the cells in a range that meet one condition.
- In a condition,
*is a wildcard that matches any run of characters. Only text can match a wildcard, so numbers and dates are left out. - Empty cells are left out as well.
Variations
Text that starts with a letter
A B C 1 Entry 2 Ana 1 3 42 4 5 Ben 6 2026-03-14 7 n/a In C2:
=COUNTIF(A2:A7, "A*")→ 1Put the letters in front of the asterisk. "*a" would count text that ends in a.
Every cell that is not empty
A B C 1 Entry 2 Ana 5 3 42 4 5 Ben 6 2026-03-14 7 n/a In C2:
=COUNTA(A2:A7)→ 5COUNTA counts text, numbers and dates alike.
Cells with numbers only
A B C 1 Entry 2 Ana 2 3 42 4 5 Ben 6 2026-03-14 7 n/a In C2:
=COUNT(A2:A7)→ 2The date counts: Excel stores dates as numbers.
Empty cells
A B C 1 Entry 2 Ana 1 3 42 4 5 Ben 6 2026-03-14 7 n/a In C2:
=COUNTBLANK(A2:A7)→ 1
Watch out for
- Numbers stored as text are counted. A number that was typed with an apostrophe, or imported as text, is text to Excel and matches the asterisk.
- A formula that shows nothing still counts. A cell whose formula returns
""looks empty but holds text of no length. To skip those, use=COUNTIF(A2:A7, "?*"), which needs at least one character.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- COUNTIF: Counts the cells that meet a condition.
- COUNTA: Counts cells that are not empty.
- COUNT: Counts cells that contain numbers.
- COUNTBLANK: Counts empty cells in a range.
- ISTEXT: TRUE if the value is text.