Excel COUNTIF: Count Cells That Contain Text

Formulas & functions · Updated November 22, 2026

The fastest way to count cells that contain any text in Excel is =COUNTIF(range,"*"), which counts every cell in the range that has at least one character (including spaces, punctuation, and numbers stored as text). For example, if you have a list in A2:A20, =COUNTIF(A2:A20,"*") returns the number of cells that are not truly empty. This article explains how that formula works, how to refine it for specific text, and what to do when the results don’t match what you expect.

What COUNTIF Actually Counts

COUNTIF takes two arguments: a range and a criteria. The criteria can be a number, a comparison operator with a number, or text. When you use the asterisk wildcard "*", Excel treats it as “any sequence of characters” — including a single letter, a digit, or even a space. The formula counts cells that contain any text, but also cells that contain numbers if those numbers are stored as text (e.g., a cell formatted as text with ‘123). It does not count cells that contain only a formula returning an empty string "" — those are considered empty for COUNTIF purposes.

How to Count Cells with Specific Text

To count cells that contain a particular substring, use the asterisk on both sides of the text. For instance, with product names in A2:A20, to count cells that contain “apple”:

  • Formula: =COUNTIF(A2:A20,"*apple*")
  • This counts cells like “apple pie”, “green apple”, and “applesauce” because the asterisks match any characters before and after the word.
  • It is case-insensitive: “Apple” and “APPLE” are both counted.

For an exact match (cell equals the text and nothing else), use =COUNTIF(A2:A20,"apple") — this counts only cells that are exactly “apple”.

Counting Cells That Are Not Empty (Including Errors)

If your data contains formulas that return errors like #N/A, the wildcard "*" will count them because they are text representations. To count cells that are not empty and do not contain errors, use the SUMPRODUCT function with ISNUMBER and ISTEXT:

  • With data in A2:A20, formula: =SUMPRODUCT(--(ISTEXT(A2:A20))) counts cells that are text.
  • To count cells that are not empty (including numbers and errors), use: =COUNTA(A2:A20)-COUNTBLANK(A2:A20) — but note COUNTA counts numbers and errors too.
  • For a true “not empty” count that excludes errors, use: =SUMPRODUCT(--(A2:A20<>"")) — this counts any cell that is not an empty string, including errors. To exclude errors, add --(NOT(ISERROR(A2:A20))): =SUMPRODUCT(--(A2:A20<>""), --(NOT(ISERROR(A2:A20)))).

Common Problems and Fixes

Several issues trip up users:

  • Spaces count as text. A cell with a single space is counted by "*". To ignore spaces, use =SUMPRODUCT(--(TRIM(A2:A20)<>"")).
  • Numbers stored as text. If a cell contains ‘123, the wildcard counts it. To count only cells that contain letters, use =SUMPRODUCT(--(ISTEXT(A2:A20))) — this ignores numeric text.
  • Wildcard characters in the text. If you need to count cells that literally contain an asterisk, escape it with a tilde: =COUNTIF(A2:A20,"~*").
  • Case-sensitive counting. COUNTIF is always case-insensitive. For case-sensitive counting, use SUMPRODUCT with EXACT: =SUMPRODUCT(--(EXACT(A2:A20,"Apple"))).

Alternatives to COUNTIF for Text Counting

For more complex conditions, consider these functions:

  • COUNTIFS — allows multiple criteria. For example, to count cells in A2:A20 that contain “apple” and in B2:B20 that contain “red”: =COUNTIFS(A2:A20,"*apple*",B2:B20,"*red*").
  • SUMPRODUCT — for array-based logic, as shown above. It is more flexible but can be slower on large ranges.
  • FILTER (Excel 365) — to return the matching cells, not just the count: =ROWS(FILTER(A2:A20,ISNUMBER(SEARCH("apple",A2:A20)))).
  • LEN and SUMPRODUCT — to count cells with a minimum length: =SUMPRODUCT(--(LEN(A2:A20)>0)) counts non-empty cells, but LEN returns 0 for truly empty cells and for cells with formulas that return “”.

How to Choose the Right Formula

Decide based on what you need:

  • If you want the simplest count of cells that are not empty (including spaces and numbers as text), use =COUNTIF(range,"*").
  • If you need to count cells containing a specific word or phrase, use =COUNTIF(range,"*word*").
  • If you need to exclude spaces or count only text entries, use SUMPRODUCT with ISTEXT or TRIM.
  • If you need multiple conditions, use COUNTIFS.
  • If you need case-sensitive matching, use SUMPRODUCT with EXACT.

Frequently Asked Questions

Does COUNTIF count cells with formulas that return text? Yes, it counts the result of the formula. If a formula returns an empty string "", COUNTIF treats it as empty and does not count it.

Why does my COUNTIF return 0 when I can see text in the cells? The cells might contain spaces or non-printing characters. Try using =SUMPRODUCT(--(TRIM(A2:A20)<>"")) to see if spaces are the issue. Also check that the cells are not formatted as numbers with a custom format that hides text.

Can I count cells that contain text but not numbers? Yes, use =SUMPRODUCT(--(ISTEXT(A2:A20))). This counts only cells that are text, ignoring numbers and dates.

How do I count cells that contain text but not specific text? Use =COUNTIF(range,"*") - COUNTIF(range,"*exclude*") to subtract the excluded ones. For example, =COUNTIF(A2:A20,"*") - COUNTIF(A2:A20,"*temp*") counts all non-empty cells except those containing “temp”.

Does COUNTIF work with wildcards in Google Sheets? Yes, the same syntax works in Google Sheets, but note that Google Sheets is case-insensitive as well. For case-sensitive matching in Google Sheets, use =SUMPRODUCT(--(EXACT(A2:A20,"Apple"))).

Why does COUNTIF count a cell with a space as text? Because a space is a character. To ignore spaces, use =SUMPRODUCT(--(TRIM(A2:A20)<>"")) as mentioned earlier.

Practical Example with Data

Suppose you have a list of fruits in A2:A10:

  • A2: “Apple”
  • A3: “Green Apple”
  • A4: “” (empty)
  • A5: “Banana”
  • A6: “apple pie”
  • A7: ” ” (space)
  • A8: “123” (text)
  • A9: “Orange”
  • A10: “” (empty)
  • =COUNTIF(A2:A10,"*") returns 7 (counts A2, A3, A5, A6, A7, A8, A9).
  • =COUNTIF(A2:A10,"*apple*") returns 3 (A2, A3, A6).
  • =SUMPRODUCT(--(ISTEXT(A2:A10))) returns 6 (excludes A7 because it’s a space? Actually ISTEXT returns TRUE for a space, so it counts 7 as well. To exclude spaces, use =SUMPRODUCT(--(TRIM(A2:A10)<>"")) which returns 6 (excludes A7).

These examples show how the choice of formula changes the result based on spaces and text-formatted numbers.

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.