Count cells not between two numbers

February 24, 2021

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

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

Essential Functions

Excel Topics

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
Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.
Copyright © 2017-2021 ExcelExplained.com