How to count cells with text in Excel
To count the cells that contain text, use COUNTIF with an asterisk as the criterion: =COUNTIF(A2:A6,"*"). The asterisk is a wildcard that matches any text, and it does not match numbers or empty cells. Changing the pattern around the asterisk counts text that starts with, ends with or contains a word.
The examples use one small list, so you can verify every result. The lessons at the end cover counting with more than one condition.
1. Count every cell with text
The criterion "*" matches text of any length. A cell that holds a number, an error or nothing at all is not counted. A cell that holds an empty string from a formula, shown as a blank, is counted, which is a common surprise.
Example: Cells with text
| A | B | C | |
|---|---|---|---|
| 1 | Item | Text cells | |
| 2 | Apple | 3 | |
| 3 | 7 | ||
| 4 | Apricot | ||
| 5 | |||
| 6 | Pear |
Apple, Apricot and Pear are text. The number 7 and the empty cell A5 are not counted.
2. Count cells that start with, end with or contain
Put the wildcard on the side where other characters may appear. "Ap*" matches text that starts with Ap, "*ar" matches text that ends with ar, and "*pp*" matches text that has pp anywhere inside. A question mark matches exactly one character, so "????" matches text with four letters. COUNTIF ignores capitalization.
Example: Text that starts with Ap
| A | B | C | |
|---|---|---|---|
| 1 | Item | Starts with Ap | |
| 2 | Apple | 2 | |
| 3 | 7 | ||
| 4 | Apricot | ||
| 5 | |||
| 6 | Pear |
Apple and Apricot start with Ap. Pear does not.
3. Count one exact word
To count cells equal to a word, use the word itself as the criterion, or point to a cell that holds it, such as =COUNTIF(A2:A6,C1). The match must be of the whole cell, so "Pear" does not count a cell that says "Pear tree". Add wildcards on both sides when you want a partial match.
Example: The word Pear
| A | B | C | |
|---|---|---|---|
| 1 | Item | Pear count | |
| 2 | Apple | 1 | |
| 3 | 7 | ||
| 4 | Apricot | ||
| 5 | |||
| 6 | Pear |
Only A6 equals Pear.
4. Count blanks and non-blanks
To count cells that are not empty, whatever they hold, use COUNTA. To count empty cells, use COUNTBLANK. Both are explained on the page for COUNT. Add the text count and the number count and you get the non-empty count, which is a handy check that no cell was missed.
5. Count with more than one condition
When the text must appear next to another condition, such as a status in another column, switch to COUNTIFS. It takes any number of pairs of a range and a criterion, and the wildcards work in every pair.
Common mistakes
Counting a word that contains a real asterisk or question mark. Excel reads them as wildcards.
Fix: Put a tilde in front, such as "~*", to match the character itself.
Text with a trailing space. "Pear " is not equal to "Pear" so it is not counted.
Fix: Clean the list with TRIM first, or use "Pear*" to include it.
Numbers stored as text are counted as text. A column of codes from an import may count differently than you expect.
Fix: Convert the cells to real numbers, or accept that "*" counts them as text.
Frequently asked questions
Does COUNTIF care about capital letters?
No. "apple" and "APPLE" are the same for COUNTIF. For a case-sensitive count, use SUMPRODUCT with EXACT.
How do I count cells that do not contain a word?
Use the not-equal operator with a wildcard, such as "<>*pear*".
How do I count unique text values?
That needs a different approach. See the guide on counting unique values in Excel.
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.
- COUNTCOUNT counts cells that contain numbers; COUNTA counts every non-empty cell; COUNTBLANK counts empty ones.
- ISBLANKISBLANK, ISNUMBER and ISERROR test what a cell contains and return TRUE or FALSE.
More on counting text cells
The guide covers COUNTIF with wildcards. This piece adds the difference between COUNTIF, COUNTA and ISTEXT counts, how to count text with two conditions, and why a count can be off by the cells that only look empty.
Which count answers which question
- COUNTIF with "*"** counts the cells that hold text and nothing els
- COUNTA counts every cell that is not empty, so it includes numbers, dates, errors and text.
- COUNT counts only the cells that hold numbers, and dates are numbers.
- COUNTBLANK counts empty cells, and also cells that hold an empty string from a formula.
For a column of 100 cells, the first three should add up to the number of non-empty cells, which is a good way to find out whether a few cells hold something unexpected.
Text with a second condition
To count the cells that start with a letter and are in a certain region, switch to COUNTIFS. Each pair is a range and a criterion, and a wildcard can sit in any text criterion.
=COUNTIFS(A2:A100,"Ap*",B2:B100,"North")
=COUNTIFS(A2:A100,"*",B2:B100,">10")Cells that only look empty
A cell can look blank and still hold a single space, or an empty string returned by a formula. COUNTIF with "*" counts both, while a cell that is truly empty is not counted. If the count is higher than you expect, select the column, press F5, choose Special and pick Constants to see what is really in there.
To test one cell in a formula, ISBLANK returns TRUE only for a truly empty cell. For more patterns, read [how wildcards work](/guides/wildcards) and [count unique values](/guides/count-unique-values-in-excel), or practice in [the conditional counting lesson](/learn/conditional-counting).