The direct answer: use =COUNTIF(range,"*text*") to count cells that contain a specific text anywhere within them. For example, =COUNTIF(A2:A10,"*apple*") counts every cell in A2:A10 that has the word “apple” as part of its content, such as “apple pie” or “green apple”.
Why COUNTIF with Wildcards Works
Excel’s COUNTIF function counts cells that meet a condition. The condition can be a number, a cell reference, or a text string. When you want to check if a cell *contains* text rather than equals it exactly, you use wildcard characters. The asterisk (*) represents any sequence of characters (including none), and the question mark (?) represents a single character. Placing an asterisk before and after your search text tells Excel to look for that text anywhere in the cell.
For instance, with data in A2:A5 as “cat”, “catalog”, “scatter”, and “dog”, the formula =COUNTIF(A2:A5,"*cat*") returns 3 because three cells contain the letters “cat” in any position.
Exact vs. Partial Match: The Key Difference
Without wildcards, COUNTIF treats text as an exact match. So =COUNTIF(A2:A5,"cat") would return 1 (only the cell that is exactly “cat”). To count cells that contain a substring, you must add the asterisks. If you want to count cells that start with a specific text, put the asterisk only at the end: =COUNTIF(A2:A5,"cat*") counts “cat” and “catalog” but not “scatter”. Similarly, to count cells ending with text, use =COUNTIF(A2:A5,"*cat").
Counting Cells That Contain Any Text (Non-Blank)
A common need is to count all cells that have *any* text, excluding blanks and numbers. The formula =COUNTIF(A2:A10,"*") counts cells that contain any text string, but it ignores numbers and logical values. If you want to count all non-empty cells (including numbers), use =COUNTA(A2:A10). If you want to count cells that contain at least one character (including spaces), use =SUMPRODUCT(--(LEN(TRIM(A2:A10))>0)) – but for most purposes, =COUNTIF(A2:A10,"*") is sufficient for text-only counting.
Case Sensitivity and Special Characters
COUNTIF is not case-sensitive. =COUNTIF(A2:A5,"*CAT*") will count the same cells as =COUNTIF(A2:A5,"*cat*"). If you need a case-sensitive count, you must use SUMPRODUCT with the FIND function: =SUMPRODUCT(--ISNUMBER(FIND("cat",A2:A5))). Note that FIND is case-sensitive, while SEARCH is not.
Wildcards also have a special role. If you actually want to count cells that contain an asterisk or question mark, you need to escape them with a tilde (~). For example, =COUNTIF(A2:A5,"*~**") counts cells that contain an asterisk anywhere. The first and last asterisks are wildcards, and the ~* tells Excel to treat the middle asterisk as a literal character.
Using Cell References in the Criteria
Often you want the search text to come from another cell. You can concatenate the wildcards with a cell reference. Assuming B1 contains the text you’re looking for, use =COUNTIF(A2:A10,"*"&B1&"*"). This works because the ampersand (&) joins the asterisk, the cell value, and the second asterisk into a single text string. If B1 is empty, the formula counts all cells containing any text (because it becomes “**”).
Common Problems and How to Avoid Them
- Counting numbers that look like text: If your cells contain numbers stored as text (e.g., “123”),
=COUNTIF(A2:A10,"*123*")will count them, but=COUNTIF(A2:A10,"123")might not because of formatting. To be safe, use the wildcard version. - Trailing spaces: If cells have extra spaces, the wildcard might not match as expected. Use TRIM in a helper column or use
=SUMPRODUCT(--ISNUMBER(SEARCH("text",TRIM(A2:A10)))). - Counting cells that contain one of several texts: You can’t do that directly with a single COUNTIF. Use
=COUNTIF(A2:A10,"*apple*")+COUNTIF(A2:A10,"*orange*"), but be aware that cells containing both will be counted twice. To avoid double counting, use=SUMPRODUCT(--((ISNUMBER(SEARCH("apple",A2:A10)))+(ISNUMBER(SEARCH("orange",A2:A10)))>0)).
Alternatives: COUNTIFS, SUMPRODUCT, and Dynamic Arrays
For multiple conditions, use COUNTIFS. For example, to count cells in A2:A10 that contain “apple” and have a value in B2:B10 greater than 5, use =COUNTIFS(A2:A10,"*apple*",B2:B10,">5"). The wildcard works the same way.
SUMPRODUCT offers more flexibility, especially for case-sensitive checks or when you need to test multiple criteria without double counting. For dynamic arrays (Excel 365), you can use =SUM(--ISNUMBER(SEARCH("text",A2:A10))), which spills a single result. However, COUNTIF remains the simplest and fastest for basic contains-text counting.
Frequently Asked Questions
How do I count cells that contain text but not numbers?
Use =COUNTIF(A2:A10,"*"). This counts cells that contain any text string, ignoring numbers, dates, and logical values. If you want to include numbers as well, use =COUNTA(A2:A10).
Can I use COUNTIF with a partial match and a cell reference?
Yes, concatenate the wildcards with the cell reference: =COUNTIF(A2:A10,"*"&B1&"*"). If B1 contains “apple”, it counts cells containing “apple” anywhere. If B1 contains a wildcard character itself, it will be treated as a wildcard unless you escape it with a tilde.
Why does my COUNTIF return 0 when I know there is text?
Check for extra spaces, non-printing characters, or that the cells aren’t actually numbers formatted as text. Use =TRIM(A2) to see the cleaned value. Also, if you’re using a wildcard like “*text*”, ensure the text doesn’t contain characters that Excel interprets as wildcards (like * or ?) – escape them with ~.
Is COUNTIF case-sensitive?
No, COUNTIF is not case-sensitive. For case-sensitive counting, use =SUMPRODUCT(--ISNUMBER(FIND("text",A2:A10))). FIND is case-sensitive, while SEARCH is not.
How do I count cells that contain text from a list of words?
You can use multiple COUNTIFs added together, but that double-counts cells with multiple matches. A better approach is =SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH(TRANSPOSE("apple";"orange";"banana"),A2:A10)),ROW(INDIRECT("1:3"))^0)>0)) – but this is complex. Simpler: use a helper column with a formula like =IF(SUMPRODUCT(--ISNUMBER(SEARCH({"apple";"orange";"banana"},A2)))>0,1,0) and sum that column.
Related
- String Array Split in Java: Methods, Examples, and Tips
- SQL String Split: How to Separate Delimited Values
- Java String.split(-1): Keep Trailing Empty Fields
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Day and Date Formulas: Show Days, Dates, and NamesIn Excel, use =DAY(A2) to return the day of the month from a date in A2, or =TEXT(A2,"dddd")…
- Insert Images in Excel Cells With the IMAGE FormulaYes. In Excel for Microsoft 365, use the IMAGE function to show an online picture inside a cell.…
- Average Between Two Dates in Excel: Formulas & TipsTo average values whose dates fall between two dates in Excel, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2). This averages numbers in B2:B100…
- Excel SUMIF with Dates: Rules, Examples, and FixesTo sum values for a date or date range in Excel, use SUMIF with a date criterion; for…
- Excel AND and OR Functions Combined: How to Use ThemYes. You can combine Excel’s AND and OR functions by nesting one inside the other. For example, with…
- Excel Named Range Not Appearing in Formula? FixesIf an Excel named range is not appearing as you type a formula, first confirm that the name…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.