Excel COUNTIF Text Value: How to Count Cells with Text

Formulas & functions · Updated November 22, 2026

To count cells that contain any text value in Excel, use =COUNTIF(range,"*?"), and to count cells with a specific text, use =COUNTIF(range,"text") or =COUNTIF(range,"*text*") for partial matches. For example, =COUNTIF(A2:A10,"*?") counts all cells in A2:A10 that contain at least one character, while =COUNTIF(A2:A10,"apple") counts only cells that exactly equal “apple”.

What COUNTIF Does and How It Treats Text

COUNTIF is a built-in Excel function that counts cells in a range that meet a single condition. The condition can be a number, a date, or a text string. When you use text as the criterion, Excel compares the cell contents to the text you provide, ignoring case by default. So =COUNTIF(A2:A10,"cat") will count both “cat” and “CAT”.

Text criteria can be entered directly in the formula (as shown above) or referenced from another cell. If you reference a cell, use the ampersand (&) to concatenate wildcards if needed, for example =COUNTIF(A2:A10,"*"&B1&"*") to count cells containing the text in B1.

Counting Cells That Contain Any Text (Non-Blank)

The most common request is to count all cells that are not empty. The formula =COUNTIF(range,"*?") works because the asterisk (*) matches any sequence of characters, and the question mark (?) matches any single character. Together, "*?" requires at least one character to exist. For example, with data in A2:A10 where A2 is “hello”, A3 is blank, A4 is “123”, A5 is ” “, A6 is “TRUE”, A7 is “”, A8 is “0”, and A9 is “text”, the formula =COUNTIF(A2:A10,"*?") returns 6 because it counts A2, A4, A5, A6, A8, and A9. Note that a cell containing only a space is counted as text because the space is a character.

If you want to count cells that are truly non-blank, including cells with numbers, you can use =COUNTA(A2:A10) instead. However, COUNTA also counts cells with formulas that return an empty string (“”), so the two functions can give different results.

Counting Cells That Contain a Specific Text String

To count cells that exactly match a text string, use the text as the criterion: =COUNTIF(A2:A10,"apple"). This counts cells that contain only “apple” and nothing else. If a cell contains “apple pie”, it will not be counted.

To count cells that contain a specific text anywhere within them, use wildcards: =COUNTIF(A2:A10,"*apple*") will count cells like “apple pie”, “green apple”, and “applesauce”. The asterisk matches any number of characters before and after the text.

To count cells that start with a specific text, use =COUNTIF(A2:A10,"apple*"). To count cells that end with a specific text, use =COUNTIF(A2:A10,"*apple").

Using Wildcards and Handling Special Characters

Excel supports three wildcards in COUNTIF: the asterisk (*) for any sequence of characters, the question mark (?) for any single character, and the tilde (~) to escape a wildcard character. For example, to count cells that contain an actual asterisk, use =COUNTIF(A2:A10,"*~**"). The first and last asterisks are wildcards, and the ~* tells Excel to look for a literal asterisk. Similarly, to count cells with a literal question mark, use =COUNTIF(A2:A10,"*~?*").

Note that wildcards only work with text criteria. If you try to use wildcards with numbers, they will be treated as literal characters. Also, if your text contains a tilde, you need to escape it as well, for example =COUNTIF(A2:A10,"*~~*").

Common Problems and How to Fix Them

One common issue is that COUNTIF does not count cells that contain numbers formatted as text if you use a numeric criterion. For example, =COUNTIF(A2:A10,5) will not count a cell that contains the text “5”. To count both, you would need a different approach, such as SUMPRODUCT.

Another problem is that COUNTIF is case-insensitive. If you need case-sensitive counting, you must use a helper column with the EXACT function or use SUMPRODUCT with EXACT. For example, =SUMPRODUCT(--EXACT(A2:A10,"Apple")) counts only cells that exactly match “Apple” with that capitalization.

If your range includes errors, COUNTIF will ignore them when counting text, but if you use wildcards, it may still count error cells? Actually, COUNTIF ignores error values in the range; it does not count them. So if a cell contains #N/A, it will not be counted even with “*?*”.

If you need to count cells that contain text but not numbers, you can use =SUMPRODUCT(--ISTEXT(A2:A10)). This counts cells that are text values, regardless of content, including empty strings from formulas.

Alternatives to COUNTIF for Text Counting

If you need to count based on multiple conditions, use COUNTIFS. For example, to count cells in A2:A10 that contain “apple” and have a value greater than 5 in B2:B10, use =COUNTIFS(A2:A10,"*apple*",B2:B10,">5").

For more complex criteria, SUMPRODUCT is a powerful alternative. For instance, to count cells that contain either “apple” or “orange”, you could use =SUMPRODUCT(--(ISNUMBER(SEARCH("apple",A2:A10))+ISNUMBER(SEARCH("orange",A2:A10))>0)). However, SEARCH is case-insensitive; use FIND for case-sensitive.

In Excel 365, you can also use the FILTER function to extract matching cells and then count them with COUNTA, but for simple counting, COUNTIF is usually the most efficient.

Frequently Asked Questions

How do I count cells that contain text but not numbers? Use =SUMPRODUCT(--ISTEXT(A2:A10)). This counts cells whose value is text, ignoring numbers and blanks. If you want to exclude cells that contain only spaces, you can use =SUMPRODUCT(--(ISTEXT(A2:A10)*(TRIM(A2:A10)<>"."))) but that is more complex.

Why does COUNTIF not count cells with formulas that return text? It does count them if the formula returns a text value. However, if the formula returns an empty string (“”), COUNTIF with “*?” will not count it because the empty string has zero characters. Use =COUNTIF(range,"*") to count cells that contain any text, including empty strings? Actually, "*" matches any text, including empty strings, but it also matches numbers? No, it only matches text. So =COUNTIF(A2:A10,"*") counts cells that contain text, including empty strings, but not numbers or blanks. This is a subtle difference.

Can I use COUNTIF to count cells that contain a specific word but not part of another word? Yes, but you need to use a more precise pattern. For example, to count cells that contain the word “cat” as a separate word, you can use =COUNTIF(A2:A10,"* cat *") plus handle start/end of cell. A simpler approach is to use a helper column with a formula like =ISNUMBER(SEARCH(" cat "," "&A2&" ")) and then count TRUE values.

Does COUNTIF work with text that has leading or trailing spaces? Yes, COUNTIF treats spaces as characters. If you want to ignore spaces, you can use TRIM in a helper column or use SUMPRODUCT with TRIM.

How do I count cells that contain text from a list of values? Use SUMPRODUCT with ISNUMBER and MATCH, or use COUNTIFS with multiple criteria. For example, to count cells that contain either “apple” or “orange”, you can use =SUMPRODUCT(--(ISNUMBER(SEARCH("apple",A2:A10))+ISNUMBER(SEARCH("orange",A2:A10))>0)).

Why does my COUNTIF return 0 when I know there is text? Check for invisible characters, non-breaking spaces, or that the cell is not actually text (it might be a number formatted as text). Use the LEN function to see the length, and use TRIM and CLEAN to remove non-printable characters.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.