Exceljet

Quick, clean, and to the point

Count cells not equal to x or y

Excel formula: Count cells not equal to x or y
Generic formula 
=COUNTIFS(rng,"<>x",rng,"<>y")
Explanation 

To count cells not equal to this or that, you can use the COUNTIFS function with multiple criteria.

In the example shown, there is a simple list of colors in column B. There are 6 cells total with a color, and a few are duplicates.

To count the number of cells that are not equal to "red" or "blue", the formula in E6 is:

=COUNTIFS(rng,"<>red",rng,"<>blue")

In this example "rng" is a named range that equals B6:B11.

How this formula works

The COUNTIFS function counts cells that meet one or more conditions. All conditions must pass in order for a cell to be counted.

The key in this case is to use the "not equals" operator, which is <>.

To add another criteria, simply add a another range / criteria pair of arguments.

Alternative with SUMPRODUCT

The SUMPRODUCT function can also count cells that meet multiple conditions.

For the above example, the syntax for SUMPRODUCT is:

=SUMPRODUCT((rng<>"blue")*(rng<>"green"))
Author 
Dave Bruns

Excel Formula Training

Learn Excel formulas and functions with concise, clear videos. Master absolute/relative addresses, dates, text, named ranges, and tools for troubleshooting. Each video comes with a practice worksheet and audio transcript. Instant access and complete 100% guarantee. Start today!

See details.

Thank You So much...You changed my life :) - Anil
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course