The COUNTIFS function can count the values in multiple ranges with multiple criteria. The function counts each given range-criteria pair and combines the result with AND logical operator. Unfortunately, there is no quick and easy way to use an OR logic with the function. On the other hand, you can an OR logic between two negative logical values equals to an AND operation. We’re going to show you how to count cells not equal to x or y in Excel using this function.
Let's take a look at both the COUNTIFS and SUMPRODUCT methods.
Using COUNTIFS to count cells not equal to x or y
The COUNTIFS function counts cells that meet the given criteria. To create a “not equal” logical statement, you should use not equal operator (<>), e.g. “<>FIRE”.
Add more range-criteria pairs into the function to establish an x or y (even or z) logic. The following example is using named ranges Type, x and y. The COUNTIFS counts cells in range Type(D3:D4) which are not equal to x(“Water”) or y(“FIRE”).
Alternative: SUMPRODUCT Function
The SUMPRODUCT function can be the Swiss Army Knife of this process with its array handling abilities. You can use the range itself in a logical statement like D3:D14<>”Water”. This statement returns an array of True and FALSE. Multiply these logical arrays in the SUMPRODUCT function to create an OR operation.
Here is another guide how the SUMPRODUCT can execute logical operations on ranges: How to sum values with OR operator using SUMPRODUCT with multiple criteria.
