ThisCalculator

Everyday

Count Cells That Do Not Contain Text in Excel

Updated · Standard formula, shown below

This article explains how to count cells that do not contain text in Excel, giving you the exact formulas and steps to get the number you need. You will learn the COUNTIF and SUMPRODUCT approaches, how to handle blanks, and common pitfalls to avoid.

Formula

The most direct way to count cells that do not contain text is to use the COUNTIF function with the criteria “<>*” (not equal to any text). The formula is:

=COUNTIF(range,”<>*”)

Here, range is the group of cells you want to check, and the criteria “<>*” tells Excel to count cells that are not equal to any text string. The asterisk (*) is a wildcard that matches any sequence of characters, so “<>*” means “not equal to any text.” This will count cells that contain numbers, dates, times, errors, or are blank.

If you want to exclude blank cells as well, you can use:

=COUNTIF(range,”<>*”)-COUNTBLANK(range)

This subtracts the blank cells from the previous count, leaving only cells that have some non-text content (numbers, dates, errors).

Worked Example

Suppose you have a list in cells A1:A10 with the following values:

  • A1: “apple”
  • A2: 123
  • A3: “” (blank)
  • A4: 45.6
  • A5: “text”
  • A6: 0
  • A7: “” (blank)
  • A8: 7/1/2023 (date)
  • A9: “hello”
  • A10: 99

Using the formula =COUNTIF(A1:A10,”<>*”), Excel counts all cells that are not text. That includes numbers (A2, A4, A6, A10), dates (A8), and blanks (A3, A7). So the result is 6 (since there are 4 numbers, 1 date, and 2 blanks). If you want to exclude blanks, use =COUNTIF(A1:A10,”<>*”)-COUNTBLANK(A1:A10), which gives 4 (numbers and date only).

When to Use This Formula

This formula is useful when you need to count how many cells in a range contain non-text values, such as numbers or dates, especially when your data includes a mix of text and numeric entries. For example, you might have a column with product codes (text) and quantities (numbers), and you want to know how many quantities are entered. It also helps in data cleaning to identify cells that are not text, which might need conversion or attention.

How to Read the Result

The result is a count of cells that do not contain text. This includes numbers, dates, times, logical values (TRUE/FALSE), errors, and blank cells (if you don’t subtract them). If you subtract blanks, the result is the count of cells with non-text, non-blank content. This number can be used to assess data completeness or to conditionally format cells that are not text.

Common Mistakes and Pitfalls

  • Forgetting that blanks are counted: The basic formula counts blank cells as “not text.” If you want only cells with actual values, subtract COUNTBLANK.
  • Using “<>” without the asterisk: The criteria “<>” alone counts cells that are not empty, including text cells. To exclude text, you must use “<>*” .
  • Case sensitivity: COUNTIF is not case-sensitive, so “apple” and “APPLE” are both considered text. This is fine for this formula.
  • Cells with formulas returning text: If a cell contains a formula that returns a text string, it is counted as text. Only the result matters, not the formula.
  • Leading/trailing spaces: A cell with a space character is considered text. To treat it as blank, you might need to trim spaces first.

Alternatives to COUNTIF

If you need more complex criteria, you can use SUMPRODUCT. For example, to count cells that are not text and not blank, you can use:

=SUMPRODUCT(–(ISTEXT(range)=FALSE)) – COUNTBLANK(range)

Or to count cells that are numbers only (excluding dates, which are numbers in Excel), you can use:

=COUNT(range)

This counts only numeric cells, but dates are also numbers, so they are included. If you need to exclude dates, you would need a more complex array formula.

Limitations of the Formula

The COUNTIF formula works well for simple ranges, but it has limitations. It cannot count cells based on formatting or font color. It also treats cells with formulas returning text as text, which might not be what you expect if the formula returns an empty string “” (which is actually text). In that case, the cell is counted as text, even though it appears blank. To handle such cases, you might need to use SUMPRODUCT with ISTEXT and additional checks.

Frequently Asked Questions

Does the formula count cells with formulas that return blank?

No, if a formula returns an empty string (“”), that is considered text, so it will be counted as text, not as a non-text cell. To count them as blank, you would need to use a different approach, like SUMPRODUCT with LEN.

How do I count cells that do not contain a specific text?

To count cells that do not contain a specific text, use =COUNTIF(range,”<>*specific*”) . For example, to count cells that do not contain “apple”, use =COUNTIF(A1:A10,”<>*apple*”) . This counts all cells that do not have “apple” anywhere in the text.

Can I use this formula with multiple ranges?

Yes, you can use multiple ranges by adding them together, like =COUNTIF(A1:A10,”<>*”)+COUNTIF(C1:C10,”<>*”) . Or use SUMPRODUCT for more flexibility.

Why does my formula count cells with spaces as text?

Because a space is a character, and any cell containing at least one character is considered text. To ignore cells with only spaces, you can use =SUMPRODUCT((TRIM(range)<>“)”)) but this gets complex. A simpler way is to clean your data first.

Does this formula work in Google Sheets?

Yes, the COUNTIF function works the same way in Google Sheets, so the formula =COUNTIF(range,”<>*”) will give the same result.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.