Excel COUNTIF Contains Text: Formula & Examples

Formulas & functions · Updated November 22, 2026

To count cells that contain a specific text string anywhere within them, use =COUNTIF(range,"*"&text&"*") or simply =COUNTIF(range,"*text*") when the text is hard-coded. For example, =COUNTIF(A2:A10,"*apple*") counts every cell in A2:A10 that contains the word “apple” anywhere, including cells like “pineapple” or “apple pie”.

How COUNTIF Handles Text Matching

COUNTIF is a core Excel function that counts cells meeting a single condition. When you use a text string as the criteria, Excel treats it as an exact match by default—so =COUNTIF(A2:A10,"apple") only counts cells that are exactly “apple” (not “Apple” or “apple pie”). To count partial matches, you must add wildcards. The asterisk (*) represents any sequence of characters, and the question mark (?) represents any single character. Placing * before and after the text tells Excel to find that text anywhere in the cell.

For a cell reference, use the ampersand (&) to concatenate the wildcards with the cell value. So if the search text is in cell D1, the formula becomes =COUNTIF(A2:A10,"*"&D1&"*"). This is the most common approach when the search term changes frequently.

Examples with Real Data

Assume you have a product list in A2:A10:

  • A2: “Red Apple”
  • A3: “Green Pear”
  • A4: “Apple Juice”
  • A5: “Banana”
  • A6: “Pineapple”
  • A7: “Apple”
  • A8: “Grapes”
  • A9: “Apple Cider”
  • A10: “Peach”

Using =COUNTIF(A2:A10,"*apple*") returns 4 because cells A2, A4, A6, and A9 contain “apple” (case-insensitive by default). If you want to count cells that start with “apple”, use =COUNTIF(A2:A10,"apple*") which returns 3 (A2, A6, A9). To count cells ending with “apple”, use =COUNTIF(A2:A10,"*apple") which returns 1 (A6) because only that cell ends with the word.

Case Sensitivity and Exact Matches

COUNTIF is not case-sensitive. It treats “APPLE”, “Apple”, and “apple” as the same. If you need a case-sensitive count, you must use SUMPRODUCT with the EXACT function. For example, with the same range, =SUMPRODUCT(--EXACT(A2:A10,"apple")) counts only cells that are exactly “apple” in lowercase. For partial case-sensitive matches, combine ISNUMBER and FIND: =SUMPRODUCT(--ISNUMBER(FIND("apple",A2:A10))) counts cells containing “apple” exactly as typed (case-sensitive). Note that FIND is case-sensitive, while SEARCH is not.

Counting with Multiple Criteria

When you need to count cells that contain one text string and meet another condition, use COUNTIFS. For instance, with product names in A2:A10 and quantities in B2:B10, to count rows where the product contains “apple” and the quantity is greater than 5, use =COUNTIFS(A2:A10,"*apple*",B2:B10,">5"). This returns the number of rows meeting both criteria. You can add more pairs of range and criteria as needed.

Common Errors and How to Avoid Them

  • Using plain text without wildcards: If you write =COUNTIF(A2:A10,"apple"), you only get exact matches. Add * around the text for partial matches.
  • Forgetting the ampersand with cell references: =COUNTIF(A2:A10,"*"&D1&"*") is correct; =COUNTIF(A2:A10,"*D1*") looks for the literal characters “D1”.
  • Wildcard characters in the search text: If your search text itself contains an asterisk or question mark, Excel will treat it as a wildcard. To count a literal asterisk, use a tilde (~) before it: =COUNTIF(A2:A10,"*~**") counts cells containing an asterisk anywhere. For a question mark, use =COUNTIF(A2:A10,"*~?*").
  • Leading/trailing spaces: If cells contain extra spaces, the wildcard may not match. Use TRIM on the data or the criteria if needed.

Alternatives to COUNTIF for Text Contains

If you need more flexibility, consider these alternatives:

  • SUMPRODUCT with ISNUMBER and SEARCH: =SUMPRODUCT(--ISNUMBER(SEARCH("apple",A2:A10))) works similarly but is case-insensitive and can handle arrays more easily.
  • FILTER function (Microsoft 365): To list matching cells instead of counting, use =FILTER(A2:A10,ISNUMBER(SEARCH("apple",A2:A10))). This returns all cells containing “apple”.
  • COUNTIFS with multiple wildcard conditions: You can count cells that contain either “apple” or “pear” by adding two COUNTIF results: =COUNTIF(A2:A10,"*apple*")+COUNTIF(A2:A10,"*pear*").

Frequently Asked Questions

How do I count cells that contain specific text but not other text?
Use COUNTIFS with multiple criteria. For example, to count cells containing “apple” but not “green”, use =COUNTIFS(A2:A10,"*apple*",A2:A10,"<>*green*"). The <> means “not equal to”.

Why does my COUNTIF return 0 when I know the text exists?
Check for hidden characters like spaces or non-breaking spaces. Also verify that your wildcards are placed correctly. If you used a cell reference, make sure the ampersand is included. Another possibility: the cell contains a number formatted as text, but your criteria is text—try using "*"&D1&"*" with D1 as a number.

Can I count cells that contain any text at all (non-blank)?
Yes, use =COUNTIF(A2:A10,"?*") to count cells with at least one character. This excludes truly empty cells but includes cells with spaces. For a more robust count, use =COUNTA(A2:A10) which counts non-empty cells including errors, but not cells with formulas that return empty strings.

Does COUNTIF work with numbers that are stored as text?
Yes, but you need to be careful. If your range contains numbers stored as text, and your criteria is a number, Excel may not match. Convert the criteria to text by using "*"&TEXT(D1,"0")&"*" or use "*"&D1&"*" if D1 is text.

How do I count cells that contain one of several possible text strings?
You can add multiple COUNTIF results, or use SUMPRODUCT with an array of search terms. For example, =SUMPRODUCT(--(ISNUMBER(SEARCH({"apple","pear"},A2:A10)))) counts cells containing either “apple” or “pear”, but note that this counts a cell only once even if it contains both.

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.