Yes, you can use COUNTIF to count cells that contain text, but the exact formula depends on what you mean by “contains text”: counting any cell with text, counting cells that contain a specific word or character, or counting cells that are not empty. The most common formulas are =COUNTIF(A2:A10,"*text*") for a specific substring and =COUNTIF(A2:A10,"?*") for any text. Below is a full breakdown of how these work, what pitfalls to avoid, and how to adapt them for real-world data.
How COUNTIF Handles Text: Wildcards and Logic
COUNTIF’s syntax is =COUNTIF(range, criteria). The criteria can be a number, a comparison (like ">10"), or a text string. For text, Excel supports three wildcard characters:
*(asterisk) matches any sequence of characters, including zero characters.?(question mark) matches any single character.~(tilde) escapes the next character, so~*matches a literal asterisk.
When you use "*text*", Excel looks for cells that contain “text” anywhere—start, middle, or end. For example, =COUNTIF(A2:A10,"*apple*") counts cells with “apple”, “pineapple”, or “apple pie”. The match is case-insensitive, so “Apple” and “APPLE” both count. This is the core of “contains” logic in COUNTIF.
Counting Any Cell That Contains Text (Not Numbers or Blanks)
If you want to count cells that have any text at all, use the criteria "?*". The question mark requires at least one character, and the asterisk allows any number after it. So =COUNTIF(A2:A10,"?*") counts cells with at least one text character. It excludes:
- Empty cells
- Cells with only numbers (e.g., 123)
- Cells with only logical values (TRUE/FALSE)
- Cells with errors (like #N/A)
However, note that a cell containing a formula that returns an empty string "" is not counted, because the result is a zero-length text. If you need to count non-empty cells regardless of type, use =COUNTA(A2:A10) instead, which counts numbers and text but not truly empty cells.
Counting Cells That Contain a Specific Word or Phrase
The classic use case is counting cells that contain a particular substring. With names in A2:A20, to count how many contain “Smith”, use:
=COUNTIF(A2:A20,"*Smith*")
This returns 3 if A2:A20 contains “John Smith”, “Smith, Jane”, and “Smithsonian”. The asterisks on both sides make it a “contains” match. If you want an exact match (the whole cell equals “Smith”), omit the wildcards: =COUNTIF(A2:A20,"Smith").
For a cell reference as the substring, concatenate with ampersands. For example, with the search term in D1, use =COUNTIF(A2:A20,"*"&D1&"*"). This works in all Excel versions.
Common Problems and How to Fix Them
Several issues trip up users when using COUNTIF with text:
- Trailing or leading spaces: If your data has spaces like “Smith “, the wildcard
"*Smith*"still matches because the asterisk covers the space. But if you use exact match, it won’t. To trim, use=COUNTIF(A2:A20,"*"&TRIM(D1)&"*"). - Case sensitivity: COUNTIF is case-insensitive. If you need case-sensitive, use SUMPRODUCT with ISNUMBER and FIND:
=SUMPRODUCT(--ISNUMBER(FIND("Smith",A2:A20))). - Counting cells that do NOT contain text: Use
=COUNTIF(A2:A20,"<>*text*")but be careful—this counts blanks too. To exclude blanks, use=COUNTIFS(A2:A20,"<>*text*",A2:A20,"?*"). - Literal question marks or asterisks: To count cells containing a literal asterisk, use
=COUNTIF(A2:A20,"~*"). - Numbers stored as text: If a cell contains “123” as text,
"?*"counts it. If you want only alphabetic text, you’d need a more complex array formula.
COUNTIF vs. COUNTIFS vs. SUMPRODUCT for Text
COUNTIF handles a single condition. For multiple conditions—like counting cells that contain “apple” AND are in column B with a value over 10—use COUNTIFS. Example: with A2:A20 containing fruit names and B2:B20 containing quantities, =COUNTIFS(A2:A20,"*apple*",B2:B20,">10") counts apples with quantity over 10.
SUMPRODUCT offers more flexibility for array operations. For case-sensitive contains, use =SUMPRODUCT(--ISNUMBER(FIND("apple",A2:A20))). For counting cells that contain any of several words, you can combine with wildcards inside SUMPRODUCT, but it gets messy. As a rule: use COUNTIF for simple contains, COUNTIFS for multiple conditions, and SUMPRODUCT only when you need case sensitivity or array logic.
Real-World Examples and Layouts
Here are three concrete scenarios with assumed layouts and results:
- Example 1: A2:A10 contains product names. To count how many contain “Pro”, use
=COUNTIF(A2:A10,"*Pro*"). If A2=”Pro X”, A3=”Pro Y”, A4=”Standard”, the result is 2. - Example 2: B2:B20 contains email addresses. To count how many contain “gmail.com”, use
=COUNTIF(B2:B20,"*gmail.com*"). The dot is a literal period, not a wildcard, so it works fine. - Example 3: C2:C15 contains full sentences. To count how many mention “urgent”, use
=COUNTIF(C2:C15,"*urgent*"). This counts “urgent matter” and “not urgent” alike.
For all examples, the formula goes in any empty cell, and the result updates automatically when data changes.
Performance and Large Data Sets
COUNTIF is fast even on large ranges, but wildcard searches with leading asterisks (like "*text") can be slower because Excel must scan every cell. On a range of 100,000 rows, a simple COUNTIF with wildcards may take a second or two. If you have millions of rows, consider using a helper column with =ISNUMBER(SEARCH("text",A2)) and then COUNTIF on that helper column, or use Power Query for heavy text analysis. Also, COUNTIF ignores cells that contain errors, so if your data has #N/A, they won’t be counted—which is usually what you want.
Frequently Asked Questions
Does COUNTIF count partial matches by default? No. Without wildcards, COUNTIF requires an exact match. To count partial matches, you must add asterisks around the text, like "*text*".
How do I count cells that contain text but ignore cells with only spaces? Spaces are text characters, so "?*" will count a cell with a single space. To exclude spaces, use =COUNTIFS(A2:A10,"?*",A2:A10,"<> ") (note the space in the second criteria) or use =SUMPRODUCT(--(TRIM(A2:A10)<>"")).
Can I use a cell reference for the text I’m searching for? Yes, concatenate: =COUNTIF(A2:A10,"*"&D1&"*"). This works in Excel 365, Excel 2021, and older versions.
Why does my COUNTIF return 0 when I see the text in the cell? Check for invisible characters like non-breaking spaces (CHAR(160)), which often come from web imports. Use =CLEAN(A2) to remove them, or use =COUNTIF(A2:A10,"*"&CHAR(160)&"*") to match them.
Is COUNTIF case-sensitive? No. For case-sensitive counting, use =SUMPRODUCT(--ISNUMBER(FIND("text",A2:A10))).
How do I count cells that contain text but not numbers? Use =COUNTIF(A2:A10,"?*")—it counts any cell with at least one text character, but a cell like “123abc” counts because it has letters. If you want cells that are purely text (no digits), you’d need a VBA UDF or a more complex array formula.
Related
- PostgreSQL: How to Execute a SQL String
- MS Access Split Form: Put the Datasheet on the Bottom
- Java split() Usage: Syntax, Examples, and Common Traps
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SAS String Functions: A Practical GuideIn SAS, string functions are character functions used to inspect, extract, join, clean, and transform text; there is…
- XLOOKUP Example With SUM in ExcelWith product names in A2:A5, monthly values in B2:D5, and the product to find in E2, use =SUM(XLOOKUP(E2,A2:A5,B2:D5)).…
- How to Filter by Year in Excel: A Clear, Practical GuideTo filter a date column to one calendar year in Excel, select your data, choose Data > Filter,…
- Excel Formula for Letter Grade: IF, IFS, VLOOKUPThe most direct Excel formula for a letter grade is a nested IF statement, for example =IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C",IF(A1>=60,"D","F")))) when…
- Unique Excel Values: Shortcuts and the UNIQUE FunctionExcel has no single keyboard shortcut that instantly lists unique values. In Microsoft 365 for Windows, the quickest…
- Excel Table Filter by Text in Another Cell?Yes. To filter an Excel table using text in another cell, add a helper column with a formula…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.