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
- Excel Calendar Template with Notes? How to Make One
- Excel Calendar Template for Content Planning
- Excel Calendar Template for Events: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF in Excel: Count Yes or NoYes. Microsoft Excel has a COUNTIF function, and you can use it to count cells that contain “Yes,”…
- Excel Sort by Highest Number: A Clear Step-by-Step GuideTo sort numbers from highest to lowest in Excel, select a cell in the number column and choose…
- Excel INDIRECT: Reference Cells on Another SheetYes. In Excel for Microsoft 365, INDIRECT can turn a text reference into a reference to a cell…
- Python String Functions: Methods, Examples, and UsesPython string functions are mainly string methods—operations such as upper(), strip(), replace(), and split() that work on text.…
- Excel Formula for the Lesser of Two Values in a CellWith the two numeric values in cells A2 and B2, enter =MIN(A2,B2) in the cell where you want…
- Excel Formula for Left of CharacterThe simplest Excel formula to extract text to the left of a specific character is =LEFT(A1, FIND("char", A1)…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.