=COUNTIFS(rng,"<>x",rng,"<>y")
If you need 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"))
