To count cells that are not between two numbers in Excel, you can use the NOTBETWEEN, COUNTIF, or SUMPRODUCT functions.
How to count cells that are not between two numbers
- Apply the =NOTBETWEEN(B3:B11, 10, 20) formula
- Press Enter
- The formula returns the number of cells that are not between two numbers
Example
In the example, we want to count the cells outside a given range using a formula. Our data set is simple; the range contains numeric values only. The range will be between 10 and 20; we try to find numbers greater than 20 and less than 10.
Formula:
=NOTBETWEEN(range, min, max)Configure the arguments:
Formula:
=NOTBETWEEN(B3:B11, 10, 20)
You can learn more about the NOTBETWEEN function.
Count cells not between two numbers using COUNTIF
You can use various methods to count cell values that are not between two numbers. In the first example, we demonstrated the power of user-defined functions. Now, let us see how to use the COUNTIF function.
Formula:
=COUNTIF(range,”<”&min)+COUNTIF(range,”>”&max)Our data set is the same as above.
Apply the following formula in cell D3:
=COUNTIF(Sales,"<"&10)+COUNTIF(Sales,">"&20)Evaluate the formula that uses logical operators:
COUNTIF(Sales,”<“&10) counts values that are less than the first criteria; in this case, the lower bound is 10; The second COUNTIF counts values above 20. The formula sums values based on the two criteria and returns 4.
Using SUMPRODUCT
You can also use the SUMPRODUCT function to count cells not between two numbers.
Generic formula:
=SUMPRODUCT((range<min)+(range>max))Type in cell D3:
=SUMPRODUCT((B3:B11<10)+(B3:B11>20))
Explanation: The formula creates two arrays that contain boolean values based on the criteria.
The first array contains values less than the lower bound, in this case, 10. If the number is less than 10, the formula returns TRUE, else gets FALSE. The second array contains values that are greater than the upper bound. If the number is greater than 20, the formula returns TRUE, else gets FALSE.
={FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE}
={FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE}Next, it converts the TRUE and FALSE values to 0s and 1s.
After the conversion, the two arrays look like this:
={0, 0, 0, 0, 0, 1, 0, 0, 1}
={0, 0, 1, 0, 0, 0, 1, 0, 1}Finally, the SUMPRODUCT function sums the 1s in the array and returns 4.
