Excel COUNTIF Not Text? Use COUNTIF with Wildcards

Formulas & functions · Updated November 22, 2026

To count cells that do not contain text in Excel, use =COUNTIF(range,"<>*") — this counts numbers, dates, and blank cells but excludes any cell containing text. For a more precise count that includes only numbers (and excludes blanks), use =COUNT(range) or =SUMPRODUCT(--ISNUMBER(range)).

Why the Simple “Not Text” COUNTIF Is Tricky

Excel’s COUNTIF function counts cells that meet a single condition. Its syntax is COUNTIF(range, criteria). The criteria can be a number, a text string, a comparison operator, or a wildcard. The challenge with counting “not text” is that Excel treats numbers, dates, times, and text differently, and the criteria “not text” is not a built-in comparison like “greater than” or “equal to”. You have to express it using wildcards or a combination of functions.

The Core Formula: COUNTIF with “<>*”

The asterisk (*) is a wildcard that matches any sequence of characters. When you put it inside the COUNTIF criteria as "<>*", it means “not equal to any text string”. This counts all cells that are not text, including numbers, dates, times, logical values (TRUE/FALSE), errors, and blank cells.

For example, with values in A1:A10:

A1: 10
A2: apple
A3: 3.14
A4: TRUE
A5: (blank)
A6: 2024-01-01
A7: #N/A
A8: 0
A9: "text"
A10: 5

Entering =COUNTIF(A1:A10,"<>*") returns 7, because the text cells (A2 and A9) are excluded, while numbers, logical, blank, error, and date cells are counted.

When You Want Only Numbers (Excluding Blanks)

If your goal is to count only numeric cells (numbers, dates, times) and ignore blanks, the COUNT function is simpler and more accurate. =COUNT(A1:A10) counts cells containing numbers, dates, or times. In the example above, it would return 5 (A1, A3, A6, A8, A10). Note that COUNT ignores text, logical values, errors, and blanks.

Alternatively, =SUMPRODUCT(--ISNUMBER(A1:A10)) does the same thing. The double unary (--) converts TRUE/FALSE to 1/0, and SUMPRODUCT sums them.

Counting Cells That Are Not Text but Not Blank Either

Sometimes you want to count cells that contain something (not blank) but are not text. This could be numbers, dates, or errors. The formula =SUMPRODUCT((A1:A10<>"")*(NOT(ISTEXT(A1:A10)))) does that. It multiplies two conditions: the cell is not empty, and it is not text. In the example, this would return 6 (A1, A3, A4, A6, A7, A8, A10 — wait, A4 is TRUE, which is not text and not blank, so it counts; A7 is error, also counts; so total is 7? Let’s check: A1, A3, A4, A6, A7, A8, A10 = 7 cells. Yes, 7.)

Using COUNTIFS for Multiple Conditions

If you need to count non-text cells that also meet another condition (e.g., values greater than 5), use COUNTIFS. For example, =COUNTIFS(A1:A10,"<>*",A1:A10,">5") counts non-text cells greater than 5. In the example, that would be A1 (10), A8 (0? no, 0 is not >5), A10 (5? not >5), so only A1 = 1. But note that COUNTIFS with wildcard criteria works the same way.

Common Pitfalls and Misconceptions

One common mistake is using =COUNTIF(range,"<>text") thinking it counts non-text. Actually, that counts cells that are not equal to the literal text “text”. So if you have “text” in a cell, it will be excluded, but any other text (like “apple”) will be included. The wildcard * is essential to match any text.

Another misconception: COUNTIF with "<>*" counts blank cells. If you want to exclude blanks, you need to add a condition for non-blank, like =SUMPRODUCT((A1:A10<>"")*(NOT(ISTEXT(A1:A10)))).

Also, note that COUNTIF is not case-sensitive, and wildcards work only with text criteria. If you use "<>*", Excel interprets the asterisk as a wildcard, not a literal asterisk.

Alternatives: SUMPRODUCT and ISNONTEXT

Excel does not have an ISNONTEXT function, but you can use NOT(ISTEXT()) in array formulas. The SUMPRODUCT approach is versatile:

  • =SUMPRODUCT(--NOT(ISTEXT(A1:A10))) counts all non-text cells including blanks.
  • =SUMPRODUCT((NOT(ISTEXT(A1:A10)))*(A1:A10<>"")) counts non-text cells that are not blank.
  • =SUMPRODUCT((ISNUMBER(A1:A10))*(A1:A10>0)) counts positive numbers only.

These formulas work in all Excel versions, but note that SUMPRODUCT may be slower on very large ranges (hundreds of thousands of rows).

Frequently Asked Questions

Does COUNTIF count blank cells as non-text?

Yes, =COUNTIF(range,"<>*") counts blank cells because a blank cell is not text. If you want to exclude blanks, use the SUMPRODUCT formula with (range<>"").

How do I count cells that contain only numbers, not text?

Use =COUNT(range) — it counts numbers, dates, and times. For a more explicit formula, use =SUMPRODUCT(--ISNUMBER(range)).

Can I use COUNTIF to count cells that are not text but contain errors?

Yes, =COUNTIF(range,"<>*") counts error cells because errors are not text. If you want to count only errors, use =COUNTIF(range,ISERROR(range))? Actually, COUNTIF cannot directly use ISERROR, but you can use =SUMPRODUCT(--ISERROR(range)).

Why does my COUNTIF with “<>*” return a number that seems too high?

Because it includes blank cells and logical values. If you only want numbers, use =COUNT(range). If you want non-text and non-blank, use =SUMPRODUCT((range<>"")*(NOT(ISTEXT(range)))).

Does this work in Google Sheets?

Yes, the same formulas work in Google Sheets. The syntax is identical, and wildcards are supported. In Google Sheets, you can also use =COUNTIF(range,"<>*").

What if I want to count cells that are text but not a specific word?

Use =COUNTIF(range,"<>word") — this counts all cells that do not equal “word”, including numbers and other text. If you want to exclude only that word but include other text, you need a different approach, like =COUNTA(range)-COUNTIF(range,"word").

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.