Count cells not between two numbers

If you are looking to count cells not between two numbers in Excel using COUNTIFS, then you are in for a disappointment. That’s because COUNTIFS uses the AND logic when comparing the criteria, resulting in an always FALSE result. And that makes sense since a number can’t be, for example, higher than 100 and lower than 80 simultaneously.

That is why I will show you two alternative formulas: one that joins two COUNTIF statements and a more elegant one using SUMPRODUCT.

For this, I have created an example of different data samples where we will count observations lower than 5 or higher than 10.

Before we begin, make sure you download the exercise file.

Formula using COUNTIF function

The syntax is straightforward:
=COUNTIF(range,"<"&low)+COUNTIF(range,">"&high)

where:
range – the range to compare against your criteria;
low – the lower limit of our interval;
high – the lower limit of our interval.

Formula to count cells not between two numbers using COUNTIF

In cell M9, I have inserted the following formula:
=COUNTIF(E9:I9,"<"&K9)+COUNTIF(E9:I9,">"&L9)

The first part checks how many cells have values below our Low value, while the second one checks how many cells contain values above our High value.

Since the operators ">" and "<" are entered as text between double quotes, you must use the ampersand symbol & to concatenate the operator with each number. If you skip this step, Excel will not understand your formula and display an error message.

You can use the same formula to count cells that don’t fall between two dates. All you need to do is replace your numbers with the correct dates, and you are good to go. You can even create dates dynamically using the DATE function.

Formula using SUMPRODUCT function

SUMPRODUCT is the hidden gem of Excel formulas. It’s so versatile that I can’t stress enough how important it is to master this function. You’ll see in a minute how simple it is to create a more elegant alternative to the COUNTIF combo.

Formula to count cells not between two numbers using SUMPRODUCT

In cell M32, I’ve used the following SUMPRODUCT formula with two logical expressions:
=SUMPRODUCT((E32:I32<K32)+(E32:I32>L32))

The first thing to notice is that we don’t need to use concatenation with cell references.

The second thing to remember is that the addition (+) sign used in SUMPRODUCT creates an OR logic.

This formula will evaluate each array and return TRUE or FALSE for each value:

=SUMPRODUCT({TRUE, FALSE, FALSE, FALSE, FALSE} + {FALSE, TRUE, TRUE, FALSE, FALSE})

As you probably know, Excel is programmed to return 1 if the condition is TRUE and 0 if FALSE. This translates our formula into:

=SUMPRODUCT({1, 0, 0, 0, 0} + {0, 1, 1, 0, 0})

Adding the the two arrays results in:

=SUMPRODUCT({1, 1, 1, 0, 0})

The last step is to sum all the items in the array and return the final result of 3.

What to do next?

You can improve your Excel knowledge even further by reading another tutorial on how to cells between two dates.

If you have additional questions about using these formulas or if you still struggle to count cells not between two numbers, please let me know by writing a comment.

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Excel courses

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.

View courses