COUNTIF can count cells that match a criterion, but it does not have a built-in criterion meaning “is not a number.” To count nonnumeric entries, use COUNTA minus COUNT; use COUNTIF with a wildcard when you specifically want text, or adjust the formula if blanks and errors should be treated differently.
What COUNTIF Does—and What It Cannot Do
In Excel for Microsoft 365 on Windows, COUNTIF has the form =COUNTIF(range,criteria). The range is the cells to examine, and the criterion is the condition they must meet. For example, with values in A2:A20, =COUNTIF(A2:A20,"apple") counts cells equal to apple, while =COUNTIF(A2:A20,">10") counts values meeting the greater-than condition.
That makes COUNTIF useful for counting matches, but its criteria are comparisons or text patterns—not data-type tests. A condition such as "<>10" means “does not match 10,” not “is not a number.” It could therefore include text, other numbers, and possibly blanks depending on the criterion and cell contents. Likewise, "<>*" is not a reliable general-purpose way to identify every nonnumeric value.
The distinction matters because a worksheet may contain actual numbers, text that looks numeric, dates, empty cells, formula results, and error values. Decide which of those categories count as “not a number” before choosing a formula.
Count Text Values with COUNTIF
If you mean text entries only—not blanks, numbers, or errors—a wildcard criterion is a convenient option. With values in A2:A20, use =COUNTIF(A2:A20,"*"). The asterisk matches text strings of any length, including text such as a name, a word, or a number stored as text. It does not count ordinary numeric values or Excel error values.
For example, suppose A2:A7 contains the number 12, the text value “12,” the word “apple,” a blank, the number 0, and the error #N/A. COUNTIF with the asterisk criterion counts two text cells: “12” and “apple.” The numeric 12 and 0 are numbers, the blank is empty, and #N/A is an error rather than text.
This is a useful distinction when imported data looks numeric but is actually stored as text. The wildcard formula counts that entry as text, not as a number. If you want to identify all cells whose underlying value is not numeric—including errors or formula results—use a different approach.
Count Nonblank Cells That Are Not Numbers
For a practical count of populated cells that are not numeric, subtract COUNT from COUNTA. With the data in A2:A20, enter =COUNTA(A2:A20)-COUNT(A2:A20). COUNTA counts nonempty cells, while COUNT counts numeric values. The difference is the number of occupied cells that COUNT does not recognize as numbers.
In the example above, COUNTA returns 5 because five cells contain something: 12, text “12,” “apple,” 0, and #N/A. COUNT returns 2 because the actual numeric values are 12 and 0. The difference is 3: the numeric-looking text, the word, and the error. This formula therefore answers “How many populated cells are not numeric?” while leaving the blank out.
COUNTA also counts cells containing errors and cells with formulas, even if a formula displays an empty string such as "". As a result, this method is appropriate when “populated” means a cell contains a value or formula. It may include formula cells that look blank, so inspect the source data if those should not count.
Decide Whether Blank Cells Count
Some people use “not a number” to mean any cell that does not contain a numeric value, including empty cells. In that case, subtract COUNT from the total number of cells in the range instead of subtracting from COUNTA. With a one-column range A2:A20, use =ROWS(A2:A20)-COUNT(A2:A20). The range has 19 rows; if COUNT finds 12 numbers, the result is 7 cells that are not numeric, including any blanks.
For a rectangular range with more than one column, count the total cells by multiplying the row and column counts. With data in A2:C20, use =ROWS(A2:C20)*COLUMNS(A2:C20)-COUNT(A2:C20). This counts blanks, text, and errors as not numeric. It also treats formulas returning an empty string as nonnumeric, since they are not numeric values.
These formulas illustrate why a definition is essential: the same worksheet can produce different correct answers depending on whether blanks belong in the total. If the intended question is “How many entries need cleaning?”, blanks may be worth tracking separately from text and errors.
Numbers Stored as Text and Other Edge Cases
A cell displaying 12 is not necessarily a numeric value. It might contain the number 12, or it might contain the text string “12.” COUNT includes the first and excludes the second. COUNTA includes both. COUNTIF with "*" counts the text version as text. This is why the appearance of a cell alone is not enough to decide how a counting formula will treat it.
Dates and times are numeric values in Excel, represented internally by numbers. COUNT includes valid date or time values, even when a cell is formatted to display a calendar date or clock time. Currency and percentage formatting also do not change the underlying type: a numeric value formatted as currency or a percentage is still counted by COUNT.
Spaces can cause a similar surprise. A cell containing one or more spaces is not truly empty; it is a text value, so COUNTA counts it and COUNT does not. If imported or manually entered data may contain stray spaces, clean or inspect those cells rather than assuming they are blank.
How to Check a Cell’s Type
If the result seems wrong, test individual cells before changing the counting formula. With a value in A2, =ISNUMBER(A2) returns TRUE for a numeric value and FALSE for text, a blank, or an error. The function is useful for checking whether a value that looks like a number is actually stored numerically.
To distinguish text from a blank or an error, select the cell and look at the formula bar, or test a specific sample with =ISTEXT(A2). A text result such as “12” returns TRUE. A genuine blank returns FALSE, as does a numeric value. Excel’s IS functions can help diagnose types, but they do not by themselves decide which categories your final count should include.
For a larger list, filter the column or inspect representative cells, especially entries pasted from websites, exported reports, or other systems. Consistent data types make later sorting, arithmetic, and comparisons more predictable.
Common Mistakes and a Reliable Choice
- Using “not equal to zero” as “not a number”: A criterion such as
"<>0"tests a comparison with zero. It does not test the cell’s data type. - Forgetting blanks: COUNTA minus COUNT excludes genuinely empty cells, while total cells minus COUNT includes them.
- Overlooking errors: COUNTA minus COUNT includes error cells among the nonnumeric entries. COUNTIF with a text wildcard does not count errors as text.
- Assuming numeric-looking text is a number: Check with ISNUMBER or count text with the wildcard formula if the distinction matters.
- Counting formula-generated empty strings unintentionally: COUNTA counts formula cells even when their displayed result is an empty string.
A quick selection guide is:
- Count actual numeric values, including dates: use COUNT.
- Count text only: use COUNTIF with
"*". - Count nonblank cells that are not numeric, including errors: use COUNTA minus COUNT.
- Count every cell that is not numeric, including blanks: subtract COUNT from the total cell count.
Frequently Asked Questions
Can COUNTIF count cells that are not numbers?
Not with a dedicated “not a number” criterion. COUNTIF can count text with a wildcard, but for all populated nonnumeric cells, COUNTA minus COUNT is usually clearer. Choose a formula based on whether blanks and errors should be included.
Does COUNTIF with an asterisk count numbers stored as text?
Yes. A value stored as text can match the wildcard text criterion even if it looks like a number. A genuine numeric value does not count as text. Use ISNUMBER to check an individual cell when unsure.
Does COUNT count dates in Excel?
Yes. Excel stores valid dates and times as numeric values, so COUNT includes them. Their date or time formatting changes how they appear, not whether they are numeric.
How do I count nonnumeric cells but ignore blanks?
Use COUNTA(range)-COUNT(range). COUNTA excludes truly empty cells and COUNT includes numeric values, so the difference counts populated nonnumeric cells. Remember that COUNTA includes error values and formulas that display an empty string.
Why does my non-number count include an error?
Errors such as #N/A are not numeric values. COUNTA counts a cell containing an error, while COUNT does not, so COUNTA minus COUNT includes it. If you want text only, use the wildcard COUNTIF approach instead.
Related
- Close Excel Shortcut Key for Windows and Mac
- Close Group Excel Shortcut: Close Workbooks and Rows
- Close Excel Shortcut: Windows and Mac Keys
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Extract Data After a Word in Excel: Formulas and StepsTo extract everything after a word in Excel for Microsoft 365, put the source text in A2 and…
- Excel Formula to Return Multiple Lookup ResultsTo return every row that matches a lookup value in Microsoft 365 Excel, use the FILTER function. For…
- PROPER Function in Excel: Examples and TipsExcel’s PROPER function capitalizes the first letter of each word and changes the remaining letters to lowercase. For…
- XLOOKUP in Excel: Compare Two Lists and Find MatchesYes. In Excel, XLOOKUP can compare a value in one list with another and return the matching item…
- COUNTIF Excel Range: What to Select and How It WorksIn Excel, the range in COUNTIF is the group of cells the function checks; the basic form is…
- Excel Calculating Hours Worked: Formulas and TipsTo calculate hours worked in Excel, subtract the start time from the end time; for a shift that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.