COUNTIF Not in Excel? Count Values That Do Not Match

Formulas & functions · Updated November 22, 2026

Excel does have the COUNTIF function; there is no separate function called COUNTIFNOT. To count cells that do not match a value, use the “not equal to” criterion <>, such as =COUNTIF(A2:A20,"<>Paid"). If blanks must not be counted, add a second criterion with COUNTIFS, as explained below.

What COUNTIF Does in Excel

COUNTIF counts cells in a range that meet one condition. Its syntax is COUNTIF(range, criteria): the range is the cells to inspect, and criteria tells Excel what to count. Criteria can be a number, text, comparison, cell reference, or wildcard pattern. The function is available in Microsoft Excel for Microsoft 365 on Windows, as well as in other current Excel editions.

The word “not” is expressed as an operator inside the criteria, rather than as a different function name. In Excel criteria, <> means “not equal to.” For example, a criterion of <>Paid means “does not equal Paid.” The operator and the value need to be supplied together as one criterion.

This pattern is useful for simple exclusions, such as counting orders that are not marked Paid, products that are not a particular type, or entries that differ from a selected value. It is important to decide whether blank cells should count as nonmatches; that detail can change the result.

Count Cells That Do Not Equal a Value

Suppose payment statuses are in B2:B20, and you want to count entries other than Paid. With this layout, the formula is:

=COUNTIF(B2:B20,"<>Paid")

This counts cells whose contents are not Paid. COUNTIF is not case-sensitive for ordinary text comparisons, so Paid, PAID, and paid are treated as the same value for this purpose. If the range contains blanks, do not assume they are excluded from every “not equal” test. A reliable way to control the blank treatment is to add a separate nonblank condition.

With statuses in B2:B20, and with the goal of counting values that are neither Paid nor blank, use:

=COUNTIFS(B2:B20,"<>Paid",B2:B20,"<>")

COUNTIFS applies both conditions to each cell in the same range: the cell must not equal Paid and must not be empty. If there are 19 cells, four say Paid, three are blank, and the remaining 12 contain other statuses, this formula returns 12. When blank cells are meaningful records that should count as “not Paid,” use a formula that explicitly includes them instead of relying on an ambiguous interpretation.

Use a Cell Reference for a Changeable Exclusion

A hard-coded word is convenient for a one-off count, but a reference makes a worksheet easier to reuse. Put the value to exclude in D2, such as Paid. With statuses in B2:B20 and the exclusion value in D2, use:

=COUNTIFS(B2:B20,"<>"&D2,B2:B20,"<>")

The ampersand joins the comparison operator to the value in D2, creating a criterion such as <>Paid. If you change D2 to Pending, the result updates without editing the formula. This is helpful in reports where a reader selects a status from a cell, or where the excluded label changes over time.

When building criteria with cell references, the operator goes in quotation marks and is joined to the reference with &. Typing <>D2 as a single quoted criterion would ask Excel to compare against the literal text D2, not the value stored in D2.

Exclude More Than One Value

To count nonblank entries that are neither Red nor Blue, add one criterion for each excluded value. Suppose the colors are in A2:A20. Use:

=COUNTIFS(A2:A20,"<>Red",A2:A20,"<>Blue",A2:A20,"<>")

All conditions in COUNTIFS must be true for a cell to count. In this example, a cell must differ from Red, differ from Blue, and be nonblank. This “all conditions” behavior is why separate not-equal criteria work naturally for excluding several values.

If the exclusion list is long or changes often, keeping exclusions in a separate range may be easier to maintain than adding many criteria by hand. With values to check in A2:A20 and excluded labels in D2:D4, the following formula counts nonblank values in A that do not appear in the exclusion list:

=SUMPRODUCT((A2:A20<>"")*(COUNTIF(D2:D4,A2:A20)=0))

Here, COUNTIF checks each entry in A against the exclusion list, and SUMPRODUCT totals the entries with no match. This array-style use of COUNTIF works in current Excel, including Microsoft 365. Keep the checked range and the exclusion list tidy, and be aware that COUNTIF treats wildcard characters in criteria specially; an exclusion label containing an asterisk or question mark may need extra handling.

Count Blank Cells or Count Everything Except a Match

“Not equal to a value” and “not blank” are different tests. A blank cell is not text such as Paid, but in many workbooks you may want blank rows excluded from a count of meaningful records. The two-condition COUNTIFS example above makes that intention explicit.

To count only empty cells in B2:B20, use:

=COUNTBLANK(B2:B20)

To count nonempty cells in the same range, use:

=COUNTA(B2:B20)

These functions answer different questions from COUNTIF: COUNTBLANK counts empty cells, while COUNTA counts cells containing a value, including text, numbers, and formulas that return text such as an empty string. If the goal is “all populated statuses except Paid,” use a nonblank condition alongside the not-equal condition rather than subtracting from the total number of rows without checking what those rows contain.

There is another useful approach when you have a fixed set of rows and need to count every row except those matching one value, including blank rows. With records in B2:B20, subtract the number of Paid cells from the number of rows in the range:

=ROWS(B2:B20)-COUNTIF(B2:B20,"Paid")

This counts each row position not containing Paid, including empty positions. It is appropriate only when every row in the range represents a record to include in the total. If unused rows should not count, use a nonblank-based method instead.

Wildcards and Text Criteria

COUNTIF criteria can use wildcards when you want to exclude a pattern rather than one exact label. An asterisk (*) matches any number of characters, and a question mark (?) matches one character. The tilde (~) is used to treat a wildcard as a literal character. For example, “not equal to this exact word” is not the same as “does not contain this word.”

For instance, a criterion such as <>*East* is intended to exclude text matching a pattern containing East. If the goal is to count cells that do not contain East anywhere in the text, verify the behavior against the data, especially where blanks and non-text values are present. Wildcards are designed for text matching, so they are not a substitute for a carefully defined rule about empty cells, numbers, or errors.

A common mistake is to use <>East when the intention is to exclude every cell containing the word East. That criterion excludes the exact value East, but a cell reading Northeast is a different value and may still qualify. Wildcard criteria are more appropriate for partial-text exclusions.

COUNTIF Compared With COUNTIFS and Alternatives

Use COUNTIF when there is one condition, such as “not Paid.” Use COUNTIFS when the count depends on multiple conditions, such as “not Paid and not blank,” or when multiple excluded labels must all be avoided. Both functions return a count; neither changes or deletes worksheet data.

For a one-time check, a filter can also help you inspect nonmatching records visually. In Excel for Microsoft 365 on Windows, select the data, then use Data > Filter and choose the relevant values or text filter. Filtering hides rows from view rather than removing them, while a formula gives a count that can update as the source data changes. On Mac, the Filter command is also on the Data tab; the exact layout can vary with the ribbon configuration.

For a larger reporting model, it can be clearer to create a helper column that labels each row as included or excluded, then count the included labels. This adds a visible explanation beside the source data and can make review easier, although it uses more worksheet space. Choose the simplest method that states the counting rule plainly and handles blanks as intended.

Common Errors and How to Fix Them

  • Using the wrong operator: Excel uses <> for “not equal to,” not the programming-language operator !=.
  • Forgetting quotation marks: Text criteria should be quoted, as in "<>Paid". Without the proper text criterion, Excel may interpret the entry differently or return an error.
  • Leaving out the ampersand: When the excluded value is in a cell, join the quoted operator and the reference with &.
  • Counting blanks unintentionally: Add a nonblank condition if empty cells should not be treated as records.
  • Expecting case-sensitive matching: COUNTIF does not distinguish capitalization in ordinary text criteria. If case matters, use a case-sensitive approach based on EXACT and array calculations, or normalize the data first.
  • Confusing exact and partial matches: <>East excludes the exact label East; it does not mean “exclude any text that contains East.”
  • Using unequal range sizes with COUNTIFS: Each criteria range should have the same dimensions, or Excel can return an error.

When a result looks wrong, check the actual cell contents as well as the formula. A cell that appears blank may contain a space, and text imported from another system may contain leading or trailing spaces. Those characters can make two values appear identical on screen while Excel treats them as different. Cleaning the source data or applying a consistent data-entry rule can prevent recurring mismatches.

Frequently Asked Questions

Is there a COUNTIFNOT function in Excel?
No. Excel has COUNTIF and COUNTIFS, and you express “not equal” with the <> criterion. Use COUNTIFS when you also need to exclude blanks or apply additional conditions.

How do I count cells that are not equal to a cell value?
Join the not-equal operator to the cell reference with an ampersand. For dependable nonblank counts, include a second criterion that excludes empty cells, as in the D2 example above.

Does COUNTIF count blank cells as not equal?
Blank handling depends on the exact criterion and counting rule, so make the intended behavior explicit. Add a nonblank criterion when blanks must not be included, or use COUNTBLANK when you specifically want the empty-cell count.

How do I count values that are not in a list?
For a short list, add one not-equal condition per excluded value in COUNTIFS. For a maintained exclusion range, use the SUMPRODUCT and COUNTIF pattern shown above, making sure to decide whether blank source cells should count.

Can COUNTIF make a case-sensitive comparison?
No. COUNTIF text matching is case-insensitive. If capitalization is significant, use a method built around the EXACT function or standardize the text before counting.

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.