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
- Excel Biweekly Timesheet Template with Lunch
- X++ String Split: How to Use str2con and Other Methods
- Excel Before Close Event: How to Run Code on Save
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Excel Contains Part of Text: Examples & TipsYes. In Excel, use COUNTIF with asterisks around the text you want to find: =COUNTIF(A2:A8,"*North*") counts cells in…
- Excel AVERAGEIF With Dates: Formula, Examples, and TipsYes—Excel’s AVERAGEIF can average values for a matching date. With dates in A2:A100, values in B2:B100, and the…
- Excel LEFT of a Character: Formula and ExamplesTo return everything to the left of a character in Excel, combine LEFT with FIND: with the text…
- Python String Functions: Methods, Uses, and ExamplesPython string functions are built-in tools and string methods for creating, inspecting, searching, splitting, and formatting text. For…
- Excel Rounding: Does It Always Round Up in Formulas?No. Excel does not always round up: it can round to the nearest value, round away from zero,…
- Do Excel Merged Cells Need to Be the Same Size to Sort?Yes. When you sort a range in Excel, any merged cells included in that range must have the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.