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 

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:


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:

Dave Bruns

Excel Formula Training

Learn key 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 to follow along. Start today - instant access, 100% guarantee.

Click for more information.

I really like Exceljet and have already referred a few colleagues to it. Your shortcuts and tips are relevant and very easy to follow/learn. Thanks for providing such a great resource! - Theresa
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