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 includes blank cells, numbers, and any other text, because the “not equal to” operator in COUNTIF treats all non-matching values as qualifying.
How the “Not Equal To” Operator Works in COUNTIF
COUNTIF takes two arguments: the range to check and a criterion. The criterion can be a number, expression, or text. To express “not equal to”, you place the angle brackets <> before the value inside double quotes. The full syntax is =COUNTIF(range,"<>value"). The comparison is case-insensitive, so “pending” and “PENDING” are treated as the same text. The operator only works with the literal text you provide; it does not support wildcards like * or ? when used with <> in the same way—those wildcards are for partial matches, not for negation.
Practical Examples with Different Data Types
Assume you have a list of project statuses in cells B2:B20. To count all statuses that are not “Complete”, use:
=COUNTIF(B2:B20,"<>Complete")— returns the number of cells that are not exactly “Complete”, including blanks and numbers.=COUNTIF(B2:B20,"<>*Complete*")— this does NOT work as expected; it counts cells that do not contain the substring “Complete” anywhere, but it also excludes blanks because blanks do not match the wildcard pattern. Use with caution.=COUNTIF(B2:B20,"<>"&D1)— if cell D1 contains the text you want to exclude, concatenate the operator with the cell reference.
For a concrete result: if B2:B20 contains 15 statuses, 10 of which say “Complete”, 3 say “In Progress”, and 2 are blank, the formula =COUNTIF(B2:B20,"<>Complete") returns 5 (the 3 “In Progress” plus the 2 blanks).
Counting Cells That Are Not Equal to a Text in Another Cell
When your exclusion text lives in a cell, say E1, you cannot write "<>E1" because Excel would treat that as the literal string “E1”. Instead, use the ampersand to join the operator with the cell reference: =COUNTIF(A2:A100,"<>"&E1). This works in all versions of Excel. If E1 contains “Closed”, the formula counts every cell in A2:A100 that is not the word “Closed”. This is the most common way to make the formula dynamic—change E1 and the count updates automatically.
Handling Blank Cells and Empty Strings
A frequent misunderstanding is that <>"text" excludes blank cells. It does not. Blank cells are not equal to any text, so they are counted. If you need to count non-blank cells that are not equal to a specific text, combine COUNTIF with COUNTA or use SUMPRODUCT. For example, to count cells that are not blank and not “N/A” in C2:C50:
=COUNTA(C2:C50)-COUNTIF(C2:C50,"N/A")— counts all non-empty cells, then subtracts the ones equal to “N/A”. This works but is clunky.=SUMPRODUCT((C2:C50<>"")*(C2:C50<>"N/A"))— returns the same result more directly. This formula works in all Excel versions.
If you have formulas that return an empty string "", those cells are not truly blank. COUNTIF treats them as text, so they will be counted when using <>"text". To exclude them, use =SUMPRODUCT((C2:C50<>"")*(C2:C50<>"N/A")) which also excludes empty strings.
Comparing COUNTIF with SUMPRODUCT and FILTER
COUNTIF is the simplest and fastest for a single condition. However, if you need to count based on multiple criteria, use COUNTIFS: =COUNTIFS(A2:A100,"<>Paid",B2:B100,"<>Void"). For more complex logic, SUMPRODUCT offers flexibility: =SUMPRODUCT(--(A2:A100<>"Paid")) returns the same as COUNTIF but allows array operations. In Excel 365, you can also use =ROWS(FILTER(A2:A100,A2:A100<>"Paid")) which returns the count of rows that meet the condition. FILTER requires Excel 365 or Excel 2021; it is not available in older versions. For most users, COUNTIF is the right choice because it is easy to read and works everywhere.
Common Errors and How to Fix Them
- Using
<>textwithout quotes:=COUNTIF(A1:A10,<>"Yes")causes a syntax error. Always put the entire operator and value inside double quotes:"<>Yes". - Forgetting the ampersand with cell references:
=COUNTIF(A1:A10,"<>"B1)is invalid. Use"<>"&B1. - Expecting case sensitivity: COUNTIF is case-insensitive. If you need case-sensitive counts, use SUMPRODUCT with EXACT:
=SUMPRODUCT(--(NOT(EXACT(A1:A10,"yes")))). - Counting blanks unintentionally: As noted, blanks are included. If you want to exclude blanks, add a condition:
=COUNTIFS(A1:A10,"<>Yes",A1:A10,"<>").
Frequently Asked Questions
Does COUNTIF “not equal” count numbers and dates? Yes. Any cell that does not match the text criterion is counted, including numeric values, dates, and error values (like #N/A). If you want to exclude errors as well, you would need to use =SUMPRODUCT((A1:A10<>"text")*ISNUMBER(A1:A10)) or similar.
How do I count cells that are not equal to one of several texts? Use COUNTIFS with multiple criteria: =COUNTIFS(A1:A10,"<>Red",A1:A10,"<>Blue"). This counts cells that are neither “Red” nor “Blue”. For a dynamic list of exclusions, consider SUMPRODUCT with MATCH.
Why does my formula return 0 when I have visible non-matching cells? Check if the cells contain leading or trailing spaces. ” Pending” is different from “Pending”. Use the TRIM function in a helper column or use =SUMPRODUCT((TRIM(A1:A10)<>"
Related
- Snowflake Split String Into Rows: SQL Examples and Tips
- Snowflake SPLIT: How to Split Strings by Delimiter
- Snowflake SQL SPLIT: Syntax, Examples, and Alternatives
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Function in Snowflake: Definition, Use & ExamplesIn Snowflake, a function is a reusable piece of SQL logic that takes input parameters, performs a defined…
- Excel Sort and Filter by Multiple CriteriaIn Excel for Microsoft 365 on Windows, sort by several criteria with Data > Sort and add a…
- Excel Table Name: Find, Change, and Use Names in FormulasAn Excel table name is the workbook-level identifier assigned to a formatted Excel table, such as Table1 or…
- How to Round Negative Numbers to Zero in ExcelIf your values are in A2 and you want every negative number to become zero while leaving zero…
- Excel Formula to Return Text Left of a Character in a CellTo return the text to the left of a character in Excel, use =LEFT(A2,FIND("-",A2)-1), replacing A2 with the…
- Outlook Rules: Filter Your Incoming Mail AutomaticallyCreate an Outlook rule by choosing a condition, such as who sent a message or a word in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.