Explanation
In this example, the goal is to count the number of cells in data (B5:B15) that are not equal to “red” or “blue”. This problem can be solved with the COUNTIFS function or the SUMPRODUCT function, as explained below.
Not equal to
The not equal to operator in Excel is <>. For example, with the number 10 in cell A1:
=A1<>5 // returns TRUE
=A1<>10 // returns FALSE
The first formula returns TRUE since A1 is indeed not equal to 5. The second formula returns FALSE since A1 is equal to 10.
COUNTIFS function
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, so you can think of COUNTIFS as using AND logic to join conditions.
In the example shown, there is a list of colors in column B in the named range data (B5:B15). We want to count cells where the color is not red or blue. To solve this problem, we need two separate conditions: (1) not equal to “red”, and (2) not equal to “blue”. The conditions given to COUNTIFS 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 <>. The conditions we need are:
data,"<>red" // not equal to "red"
data,"<>blue" // not equal to "blue"
When we place these conditions in COUNTIFS, the result is 4, since there are 4 cells that contain colors not equal to “red” or “blue”:
=COUNTIFS(data,"<>red",data,"<>blue") // returns 4
To exclude other colors, you can 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((data<>"blue")*(data<>"green"))
This is an example of using Boolean algebra with multiplication for OR logic .
Not equal to many things
The formulas above do scale well as you add more conditions. To count cells not equal to many things with a more streamlined formula, see this example .
Explanation
In this example, the goal is to count the number of cells in a range that are over a certain number of characters in length, where the number ( n ) is provided as a variable in cell F4. This problem can be solved with the SUMPRODUCT and LEN functions like this:
=SUMPRODUCT(N(LEN(B5:B15)>F4)) // returns 5
The formula returns 5 since there are five cells in B5:B15 that contain more than 40 characters.
Reference calculation
The formula in C5, copied down, is based on the LEN function :
=LEN(B5) // returns 25
This calculation is provided for reference only and is not used by the formula above. The counts in column C make it easy to quickly check results.
Checking length
Working from the inside out, the number of characters in each cell is calculated with the LEN function like this:
LEN(B5:B15)
The LEN function runs on the range B5:B15. Because we give LEN multiple values, it returns multiple results in an array like this:
{25;47;46;45;42;32;36;34;36;40;46}
Each number in the array is the length of a cell in B5:B15. This array is evaluated with the logical expression “>F4”, which creates an array of TRUE FALSE values:
{FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}
Each TRUE corresponds to a cell that contains more than 40 characters, since cell F4 contains 40. To convert the TRUE and FALSE values to their numeric equivalents, we use the N function :
N({FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE})
Note: the double negative (–) is another way to convert TRUE/FALSE to 1/0.
The result is an array of 1s and 0s:
{0;1;1;1;1;0;0;0;0;0;1}
Counting results
This array is returned directly to the SUMPRODUCT function , which returns the sum of numbers in the array:
=SUMPRODUCT({0;1;1;1;1;0;0;0;0;0;1}) // returns 5
The final result is 5. Since n is provided as a variable in cell F4, it can be changed at any time and the formula will recalculate and return a new result.
COUNTIFS function
This is an example of a problem that can’t be solved directly with the COUNTIFS function . This is because COUNTIFS requires a range and won’t allow processing of an array like that returned by the LEN function above. However, if you don’t mind using a helper column , you could use COUNTIFS on column C like this:
=COUNTIFS(C5:C15,">"&F4) // returns 5
The result is the same as the SUMPRODUCT formula above. Note the greater than operator (>) is enclosed in double quotes ("") and concatenated to F4.
