Excel COUNTIF Not Equal to Text: Formula & Fixes

Formulas & functions · Updated November 22, 2026

To count cells in Excel that do not equal a specific text value, use the formula =COUNTIF(range,"<>text"). For example, =COUNTIF(A2:A10,"<>Pending") counts every cell in A2:A10 that is not exactly the word “Pending”. This works in Excel for Microsoft 365, Excel 2021, 2019, and earlier versions on Windows and Mac.

How the “Not Equal” Operator Works in COUNTIF

The COUNTIF function takes two arguments: a range and a criterion. The criterion is a text string that defines which cells to count. To express “not equal to,” you use the <> operator inside the criteria, wrapped in double quotes. Excel treats "<>text" as a comparison that returns TRUE for any cell whose value is not exactly equal to the text.

Key details:

  • The comparison is case-insensitive. "<>pending" counts cells containing “Pending”, “PENDING”, or “pEnDiNg” as equal, so those cells are excluded.
  • Wildcards are not allowed with the <> operator in the same way as with "text*". If you need to exclude cells that contain a substring, you must use SUMPRODUCT or COUNTIFS with wildcards.
  • Cells that are empty are counted as “not equal to” any text, because an empty cell is not equal to “Pending”. If you want to exclude blanks, you need an additional condition.

Using COUNTIFS for Multiple “Not Equal” Conditions

If you need to count cells that do not equal one text and also do not equal another, use COUNTIFS. For example, with data in A2:A20, to count cells that are neither “Pending” nor “Cancelled”:

=COUNTIFS(A2:A20,"<>Pending",A2:A20,"<>Cancelled")

This counts every cell that meets both criteria. Note that COUNTIFS also ignores blank cells only if they fail the criteria; an empty cell is not equal to “Pending” and not equal to “Cancelled”, so it would be counted. To exclude blanks, add a third criterion: A2:A20,"<>".

Handling Blank Cells and Empty Strings

A common misconception is that COUNTIF(range,"<>text") excludes blanks. It does not. In Excel, an empty cell has a value of 0 in some contexts, but for text comparisons, it is treated as an empty string. Since an empty string is not equal to “Pending”, it is counted.

To count only non-blank cells that do not equal a specific text, use:

=COUNTIFS(A2:A20,"<>Pending",A2:A20,"<>")

The second criterion "<>" means “not equal to nothing”, which effectively excludes blanks. Alternatively, use =COUNTA(A2:A20)-COUNTIF(A2:A20,"Pending") if you are certain there are no other text values you want to exclude.

Comparing with Other Functions: SUMPRODUCT and FILTER

For more complex conditions, such as excluding cells that contain a certain substring, COUNTIF is not sufficient. Instead, use SUMPRODUCT with ISNUMBER and SEARCH. For example, to count cells in A2:A10 that do not contain the word “Done” anywhere:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Done",A2:A10))=FALSE))

This works by checking each cell for the substring “Done” and then counting the FALSE results. Note that SEARCH is case-insensitive; use FIND for case-sensitive matching.

If you have Excel 365 or Excel 2021, you can also use the FILTER function to return the actual cells, then count them with COUNTA:

=COUNTA(FILTER(A2:A10,A2:A10<>"Pending"))

This returns the count of cells that are not “Pending”, excluding blanks automatically because FILTER drops empty cells when the condition is false.

Common Errors and How to Fix Them

  • #VALUE! error: This usually happens if the criteria syntax is incorrect, such as missing quotes or using a cell reference without concatenation. If you want to reference a cell that contains the text, use =COUNTIF(A2:A10,"<>"&B1).
  • Counting blanks unintentionally: As noted, add a second criterion "<>" to exclude empty cells.
  • Case sensitivity issues: COUNTIF is not case-sensitive. If you need case-sensitive comparison, use SUMPRODUCT with EXACT: =SUMPRODUCT(--(EXACT(A2:A10,"Pending")=FALSE)).
  • Text with leading/trailing spaces: If your data has extra spaces, “Pending ” will not equal “Pending”. Use the TRIM function in a helper column or use SUMPRODUCT with TRIM.

Practical Examples with Sample Data

Assume you have a list of order statuses in A2:A10:

A2: Pending
A3: Shipped
A4: Pending
A5: Delivered
A6: 
A7: Cancelled
A8: Pending
A9: Shipped
A10: Delivered

To count orders that are not “Pending”, use =COUNTIF(A2:A10,"<>Pending"). The result is 6, because the three “Pending” cells are excluded, and the blank cell A6 is counted (since it is not equal to “Pending”). If you want to exclude the blank, use =COUNTIFS(A2:A10,"<>Pending",A2:A10,"<>") which returns 5.

Frequently Asked Questions

How do I count cells that do not equal a number?

Use the same syntax without quotes: =COUNTIF(A2:A10,"<>5"). This counts all cells that are not equal to 5, including text and blanks. To exclude blanks, add a condition for non-blank.

Can I use a cell reference for the text in COUNTIF?

Yes. Use the ampersand to concatenate: =COUNTIF(A2:A10,"<>"&B1) where B1 contains the text you want to exclude. This is useful when the text changes.

Why does COUNTIF count cells with formulas that return an empty string?

If a formula returns "" (an empty text string), that is not the same as a truly blank cell. COUNTIF treats "" as a text value that is not equal to “Pending”, so it gets counted. To exclude such cells, use =COUNTIFS(A2:A10,"<>Pending",A2:A10,"<>""") but note that escaping quotes in Excel can be tricky. A simpler approach is to use SUMPRODUCT with LEN.

Is there a way to count cells that do not contain a specific word anywhere?

Yes, use SUMPRODUCT with ISNUMBER and SEARCH as shown above. For example, =SUMPRODUCT(--(ISNUMBER(SEARCH("Done",A2:A10))=FALSE)) counts cells that do not contain “Done” as a substring.

Does COUNTIF work with wildcards for “not equal”?

No. The <> operator does not support wildcards. If you try "<>*Done*", Excel will treat it as a literal string, not as a wildcard pattern. Use SUMPRODUCT for substring exclusions.

Alternatives and Performance Considerations

For large datasets (tens of thousands of rows), COUNTIF and COUNTIFS are fast because they are optimized. SUMPRODUCT can be slower because it performs array calculations. If you need to exclude substrings across a huge range, consider using a helper column with ISNUMBER(SEARCH()) and then COUNTIF on that column.

Another alternative is to use the FILTER function in Excel 365, which is dynamic and can spill results. However, if you only need a count, COUNTIFS is usually the most straightforward and compatible with older versions.

Remember that all formulas must start with an equals sign, use commas as argument separators, and use straight double quotes for text criteria. On Mac, the same formulas work; keyboard shortcuts differ, but the syntax is identical.

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.