Count cells not equal to x or y

The COUNTIFS function counts the number of cells in a range that meet one or more supplied criteria. All conditions must pass in order for a cell to be counted.

In the example shown, there is a list of colors in column B in a named range called rng. We want to count cells where the color not red or blue. To solve this problem, we need two separate conditions: (1) not equal to "red", and (2) not equal to "blue".

Criteria are supplied with range/criteria pairs, and can use logical operators. The key in this case is to use the "not equals" operator, which is <>:

rng,"<>red" // not equal to "red"
rng,"<>blue" // not equal to "blue"

To exclude other colors, add additional range/criteria pairs.

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"))

This is an example of using Boolean algebra with multiplication for OR logic.