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

Formulas are the key to getting work done in Excel. In this accelerated video course, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn powerful skills to troubleshoot, trace errors, and fix problems. This is the formula training you should have had to begin with. See details here.

I LOVE your site. It helps me frequently as I have no formal training but I'm very logic oriented and this site is put together so very well and has assisted me so very much! You've improved the quality of my life. -Mike
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