Excel COUNTIF Range Contains Text: Formula Guide

Formulas & functions · Updated November 22, 2026

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

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.