COUNTIF Excel Not Equal: Formulas, Blanks, and Examples

Formulas & functions · Updated November 18, 2026

To count cells that are not equal to a value in Excel, use =COUNTIF(A2:A20,"<>Apple"). The <> operator means “not equal to”; if you need to exclude blank cells too, add a second condition with COUNTIFS.

How the not-equal operator works

Excel’s COUNTIF function counts cells in a range that meet one condition. Its syntax is COUNTIF(range, criteria): the range is where Excel looks, and the criteria describes what to count. In a criteria string, <> means “not equal to.” Put the operator and the value together inside straight double quotation marks.

For example, with product names in A2:A20, =COUNTIF(A2:A20,"<>Apple") counts cells whose contents are not Apple. The criteria is text supplied to the function, so the operator and text belong inside the quotes. A common mistake is to leave out the quotation marks or place them only around the word, which prevents Excel from reading the criterion as intended.

COUNTIF is not case-sensitive for text comparisons: Apple, APPLE, and apple are treated as the same text for this purpose. The function compares cell contents, not their font, fill color, or other formatting. If you want to count by formatting, COUNTIF is not the right tool.

Count cells that differ from a text value

Suppose A2:A9 contains Apple, Pear, Apple, Banana, a blank cell, Pear, Grape, and Apple. With those entries in A2:A9, =COUNTIF(A2:A9,"<>Apple") returns 5: four cells contain a different fruit, and the blank cell also meets this not-equal criterion. That blank-cell behavior is easy to miss when you expect the result to include only other fruit names.

If blanks should not count, use two conditions on the same range. With the same values in A2:A9, =COUNTIFS(A2:A9,"<>Apple",A2:A9,"<>") returns 4. The first condition excludes Apple; the second requires the cell to be nonblank. COUNTIFS applies both conditions, so a cell must satisfy each one to be counted.

This distinction matters in lists that contain unused rows, incomplete records, or formulas returning empty text. Decide whether an empty cell means “not Apple” for your task or whether it should be left out. When the intended result is a count of other actual entries, adding the nonblank condition makes the formula’s purpose clear.

Use a cell reference instead of a fixed value

For a reusable formula, put the value to exclude in a separate cell. With product names in A2:A20 and the excluded product in D1, use =COUNTIF(A2:A20,"<>"&D1). If D1 contains Apple, Excel builds the criterion “not equal to Apple.” The ampersand joins the operator text to the value in D1.

With the same layout, if you also want to ignore blanks, use =COUNTIFS(A2:A20,"<>"&D1,A2:A20,"<>"). This approach is useful when a report user may change the excluded item without editing the formula. It also makes the chosen value visible on the sheet.

When the reference contains a number, the same pattern works. With quantities in B2:B20 and the number to exclude in D1, use =COUNTIF(B2:B20,"<>"&D1). For example, if D1 contains 0, the formula counts values other than zero, subject to the same consideration about blanks. Keep the criteria operator inside quotation marks and join the cell reference outside them.

Count values not equal to a number or date

For a fixed number, put the operator and number in quotes as the criteria. With scores in B2:B20, =COUNTIF(B2:B20,"<>0") counts cells whose values are not zero. If the range can contain blanks and those should not be included, use =COUNTIFS(B2:B20,"<>0",B2:B20,"<>"). Check how your data represents zero: a numeric zero is different from text that looks like a zero.

Dates are stored as numbers in Excel, even though the cells are often displayed in a date format. For a fixed date, it is usually clearer to refer to a cell containing the date rather than type a date string into the formula. With dates in C2:C20 and the date to exclude in D1, use =COUNTIF(C2:C20,"<>"&D1). Make sure D1 contains a real Excel date value, not text that merely looks like a date. Add a second <> condition if blanks should be excluded.

When a result looks wrong, check the underlying values as well as their display. A cell might contain a number stored as text, a date stored as text, or a formula that returns an empty string. Formatting can make different underlying values look alike, but COUNTIF evaluates the stored contents rather than what the cell appears to show.

Use not equal with additional conditions

COUNTIF handles one criterion. If a count must meet more than one condition, use COUNTIFS, which takes range-and-criteria pairs. For example, it can count records for one region while excluding one status. Conditions supplied to COUNTIFS are combined: a row must meet every condition to be counted.

With regions in A2:A20 and statuses in B2:B20, =COUNTIFS(A2:A20,"North",B2:B20,"<>Closed") counts records in North whose status is not Closed. If B2:B20 may contain blanks and blank statuses should not count, use =COUNTIFS(A2:A20,"North",B2:B20,"<>Closed",B2:B20,"<>"). This keeps the test for region and both status requirements in one formula.

Ranges in a COUNTIFS formula should have matching dimensions so Excel can evaluate corresponding records together. For example, use A2:A20 and B2:B20, not A2:A20 and B2:B25. If you find yourself trying to express “not this value, or this other value,” remember that COUNTIFS combines criteria with AND logic, not OR logic. Separate counts or another formula structure may be needed for an either/or question.

Wildcards and special characters

In text criteria, Excel supports wildcards: an asterisk (*) matches any number of characters, and a question mark (?) matches one character. For instance, with codes in A2:A20, =COUNTIF(A2:A20,"<>AB*") counts entries that do not begin with AB. It excludes any text starting with those two letters, not just the exact text AB.

To count cells that are not exactly one text value, use the ordinary criterion without wildcards, such as <>AB. If the value you need to match literally contains an asterisk or question mark, put a tilde (~) before that character in the criterion. For example, with text in A2:A20, =COUNTIF(A2:A20,"<>~*") counts entries other than a literal asterisk. The tilde tells Excel to treat the following wildcard character as an ordinary character.

Wildcards apply to text matching; they are not a way to compare numeric ranges. If you mean “not equal to 10,” use <>10, rather than adding a wildcard. When a wildcard-based result surprises you, inspect the criterion for an accidental asterisk or question mark, since either can broaden what Excel matches.

Common problems and how to fix them

  • Missing quotation marks: A comparison operator in a COUNTIF criterion is normally supplied as text, such as "<>Apple". For a cell reference, join the quoted operator to the reference with &.
  • Blanks are included: A not-equal test against a specific value can count empty cells. Add a second condition of "<>" with COUNTIFS when the count should include only nonblank entries.
  • Hidden spaces or inconsistent entries: “Apple” and “Apple ” may not be identical because the latter has a trailing space. Check for extra spaces and inconsistent data before changing the counting formula.
  • Numbers stored as text: A cell containing text that looks like a number may not behave like a true numeric value in comparisons. Check the data type and convert values consistently if needed.
  • Unexpected errors in the source range: Error values are not ordinary text or numbers. If the range contains errors, investigate them separately rather than assuming a not-equal count treats them as normal entries.

In Microsoft 365 for Windows, enter the formula in a cell and press Enter. Excel normally adjusts relative references when you copy the formula to another row; use absolute references such as $D$1 if the comparison value must stay fixed when copied. On Mac, the formulas work the same way, though shortcut keys and some ribbon details can differ. Excel for the web also supports COUNTIF and COUNTIFS, but the desktop and web interfaces are not identical.

Frequently Asked Questions

What does <> mean in an Excel formula?
The operator <> means “not equal to.” In a COUNTIF criterion, combine it with the value to exclude, such as "<>Paid".

How do I count cells not equal to a cell reference?
With items in A2:A20 and the value to exclude in D1, use =COUNTIF(A2:A20,"<>"&D1). The ampersand joins the not-equal operator to the value in D1.

Does COUNTIF not equal count blank cells?
A criterion such as "<>Apple" can count blanks because an empty cell is not Apple. To exclude blanks, add a second condition such as "<>" in COUNTIFS.

Is COUNTIF not equal case-sensitive?
No. COUNTIF’s ordinary text comparison is not case-sensitive, so Apple and APPLE are treated as equal. Use a different approach if the task requires distinguishing letter case.

Can COUNTIF count values not equal to zero?
Yes. With numbers in B2:B20, =COUNTIF(B2:B20,"<>0") counts values other than zero. Add a nonblank condition if empty cells should not be part of the result.

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.