Excel COUNTIFS Not Equal to Text: Formula Examples

Formulas & functions · Updated November 18, 2026

To count cells that are not equal to specific text, use COUNTIFS with the not-equal operator inside quotation marks: =COUNTIFS(A2:A100,"<>Closed"). Add a second criterion of "<>" on the same range if you want to exclude blank cells as well as the text Closed.

How COUNTIFS handles not-equal text

In Excel, the not-equal operator is <>. For a text comparison, combine that operator and the text in one criteria string, such as "<>Closed". COUNTIFS checks each cell against the condition and counts the cells that meet it. The match is for the whole cell value, not simply a search for those letters inside a longer entry.

For example, a cell containing Open or Pending meets the criterion "<>Closed". A cell containing exactly Closed does not. A cell containing Closed – late is different from the exact value Closed, so it meets that criterion too. COUNTIFS text comparisons are not case-sensitive: Closed and CLOSED are treated as the same text for this purpose.

The formula’s range is the set of cells to evaluate; its criterion tells Excel which cells qualify. COUNTIFS can evaluate several range-and-criterion pairs, but every criteria range must have the same dimensions. If one range covers rows 2 through 100, the other ranges should cover those same rows.

Count nonmatching cells without counting blanks

A blank is not the text Closed, so a not-equal condition can include blank cells in its count. If your question is “How many cells contain something other than Closed?” rather than “How many cells are not Closed?”, explicitly exclude blanks with a second criterion.

With statuses in A2:A11, assume the values are Open, Closed, Pending, Open, blank, Closed, Hold, Open, blank, and Pending. The first formula counts eight cells: six nonblank values other than Closed plus two blanks. The second counts six, excluding both Closed and the blank cells.

With statuses in A2:A11, use this to count everything other than the exact text Closed, including blanks:

=COUNTIFS(A2:A11,"<>Closed")

With the same statuses in A2:A11, add the not-blank condition to count only nonblank values other than Closed:

=COUNTIFS(A2:A11,"<>Closed",A2:A11,"<>")

The second condition uses the same range and requires the cell to be nonblank. This is a useful habit when blank rows might otherwise inflate a count. It also makes the intended meaning of the result clearer to someone reviewing the worksheet.

Use a cell reference for the text to exclude

You do not have to type the excluded value directly into the formula. A cell reference makes the count easier to update: change the value in the reference cell and the result adjusts without editing the formula. The ampersand joins the operator to the referenced value, producing a criteria string such as <>Closed.

With statuses in A2:A100 and the text to exclude in D1, use:

=COUNTIFS(A2:A100,"<>"&D1,A2:A100,"<>")

Here, D1 contains Closed, and the second pair excludes blanks. Do not write "<>D1": inside quotation marks, D1 is literal text, not a cell reference. If D1 is empty, joining it to the operator creates the not-blank criterion <>. That means this formula counts nonblank cells, which may or may not be the result you intended.

Combine not-equal text with other conditions

COUNTIFS is particularly useful when the count depends on more than one condition. For example, you can count rows where the status is not Closed and the region is West. The conditions are applied together, so a row must satisfy both to be counted. This is an AND relationship, not an either/or choice.

With statuses in A2:A100 and regions in B2:B100, count rows that are not Closed and are in the West region:

=COUNTIFS(A2:A100,"<>Closed",B2:B100,"West")

If blank statuses should not qualify, add another pair for A2:A100 with the criterion "<>". Keep the ranges aligned: using A2:A100 for status and B2:B99 for region gives COUNTIFS ranges with different sizes and causes an error. In larger worksheets, check that every pair starts and ends on the same rows.

Exclude text that contains a word

There is a difference between excluding a cell exactly equal to Closed and excluding every cell that contains the word Closed. COUNTIFS criteria support the asterisk wildcard, which represents any number of characters. Put it before and after a word to match that word anywhere in the cell.

With text entries in A2:A100, count nonblank cells that do not contain Closed anywhere:

=COUNTIFS(A2:A100,"<>*Closed*",A2:A100,"<>")

This excludes values such as Closed – late as well as Closed, while keeping unrelated entries such as Open. By contrast, "<>Closed" excludes only a cell whose full content matches Closed; it does not exclude a longer phrase merely because that phrase contains the word.

The question mark wildcard represents one character. If the text you are looking for literally contains an asterisk or question mark, precede that wildcard with a tilde so Excel treats it as a character rather than a pattern. Wildcards are useful for inconsistent labels, but broad patterns can exclude more values than expected, so check the actual entries before relying on a result.

Text details that affect the result

COUNTIFS does not distinguish text by capitalization. If the difference between Closed and CLOSED matters to your task, COUNTIFS alone is not a case-sensitive test. A separate approach using EXACT with an array calculation may be appropriate, but it requires more care than the straightforward not-equal criterion.

Leading and trailing spaces are another common source of surprising counts. A cell containing Closed followed by a space is not an exact match for Closed, so it can be counted by "<>Closed". If copied or imported data may have extra spaces, inspect representative cells and consider cleaning the source values before counting. The TRIM function can remove ordinary leading and trailing spaces in a helper column, but it does not resolve every possible nonprinting character.

Also check whether values that look like text are actually numbers, dates, or formulas returning an empty string. Excel stores dates as numeric values, and a displayed format does not necessarily reveal the underlying value. A formula returning "" can look blank while still being the result of a formula; test the data and decide whether your definition of blank should include such cells.

Common COUNTIFS mistakes and fixes

  • Leaving the operator out of the criteria text: use "<>Closed", with the operator and comparison value together in the quoted criterion.
  • Putting a cell address inside quotes: use "<>"&D1 to refer to D1, rather than typing the address as part of a quoted string.
  • Forgetting blanks: add a not-blank criterion if empty cells should not count as nonmatches.
  • Using ranges of unequal size: make each criteria range cover the same rows and columns.
  • Expecting a partial-text exclusion: use a wildcard pattern such as "<>*Closed*" when the word may appear within a longer entry.
  • Assuming case matters: standard COUNTIFS text comparisons are case-insensitive.

If a result looks wrong, filter the source range or inspect a few cells that should qualify and a few that should not. This often reveals blank rows, extra spaces, slightly different labels, or a misaligned criteria range more quickly than changing the formula repeatedly.

When COUNTIFS is the right choice

Use COUNTIFS when you want a count based on one or more conditions, especially when several conditions must be true for the same row. It is more readable than manually adding separate counts for individual categories, and it adapts well when a workbook has a clear table layout. For a single condition, COUNTIF can also count values not equal to text; COUNTIFS is a natural choice when you expect to add more conditions.

For an either/or question, such as counting rows that are either Open or Pending, two criteria do not automatically create an OR condition. You can add separate counts or design a formula suited to the specific logic. Be careful if categories overlap, since adding counts can count the same row more than once. Choose the formula based on the question being asked, and verify it against a small sample where you can manually check the expected total.

Frequently Asked Questions

How do I count cells that are not equal to a word in Excel?
Use COUNTIFS with a criterion such as "<>Closed". Add a second not-blank condition on the same range if blank cells must be excluded.

Does COUNTIFS ignore blank cells with a not-equal criterion?
Do not assume blanks are excluded when comparing a range with a specific value. To make the count explicitly exclude blanks, add the same range with the criterion "<>".

How can I reference the word to exclude from another cell?
Join the not-equal operator to the reference with an ampersand, as in "<>"&D1. The referenced cell should contain the comparison text.

Is COUNTIFS case-sensitive?
No. Its normal text criteria do not distinguish uppercase and lowercase letters. Use a case-sensitive approach based on EXACT if capitalization must affect the result.

How do I exclude cells containing a word, not just equal to it?
Use asterisks around the word in the criterion, such as "<>*Closed*". Add a not-blank criterion too if empty cells should not be counted.

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.