How to count cells between two numbers in Excel

To count cells between two numbers in Excel, you can use the COUNTIFS function or the COUNTBETWEEN function.

This tutorial is a part of our definitive guide on Excel formulas.

How to count cells between two numbers?

Here are the steps to count the number of cells between two numeric values in a selected range:

Open Excel.
Type =COUNTIFS(range, “>=lower_limit”, range, “<=upper_limit”)
Press Enter.
The formula will return the count of cells within the specified range that fall between the two numbers.

Explanation

If you are unfamiliar with user-defined functions, you can use the COUNTIFS function to count the number of cells that contain values between two numbers. COUNTIFS uses range/criteria pairs, so it is easy to define the lowest and the highest numbers between the values the function will count.

The general formula is:

=COUNTIFS(range, criteria1, range, criteria2)

In the example, we want to count the cells between two numbers. The lower band is 3000; the upper band is 7000. COUNTIFS uses multiple criteria. To include the lower and upper limits, use logical operators and the following conditions:

Criteria 1 (“>=”&3000): This part checks for values in the Sales range that are greater than or equal to 3000. The & symbol concatenates the comparison operator “>=” with the number 3000.
Criteria 2 (“<=”&7000): This part checks for values in the Sales range that are less than or equal to 7000. Again, & combines the “<=” operator with 7000.

Formula:

=COUNTIFS(Sales,”>=”&3000, Sales,”=<”&7000) = 4

If the given value in the selected range meets both criteria, COUNTIFS will count the cell.

COUNTBETWEEN function

COUNTBETWEEN is a powerful user-defined function. Use it to create easily readable formulas instead of using built-in functions.

Syntax:

=COUNTBETWEEN(cRange, lowB, upB, [iLimits])

Arguments:

The function uses three required and one optional argument.

cRange is a range of cells that contains numeric values.
lowB is the lower bound
upB is the upper bound
iLimits is an optional argument; it controls that the functions count the lower and upper limits.

Example

In the first example, your data set is in cell B3:B11, and you want to count cells between 3000 and 5000. So first, select the range that contains values and add a name to it, for example, “Sales.” From now on, “Sales” refers to the B3:B11 range.

Configure the arguments of the COUNTBETWEEN function:

range = “Sales”
lower limit = 3000
upper limit = 5000

Formula:

=COUNTBETWEEN(Sales, 3000, 5000)

Evaluate the formula. COUNTBETWEEN creates an array that meets the given criteria: the value is greater than or equal to 3000 and less than equal to 3000. Without using the optional iLimit argument, the return array contains the following numbers:

={3000, 4500, 4000, 4000, 5000}

Finally, the formula counts the values in the array. So, the result is 5.

If you want to exclude the lower and upper limits (in other words, you want to apply the “greater than” and “less than” criteria), set the optional argument to FALSE.

Formula:

=COUNTBETWEEN(Sales, 1000, 5000, FALSE) = {4500, 4000, 4000} = 3

The result is 3; the formula does not count the lower and upper limits (3000 and 5000).

Code:

Function COUNTBETWEEN(ByVal cRange As Range, _
ByVal lowB As Single, ByVal upB As Single, _
Optional ByVal iLimits As Boolean = True) As Variant

Dim currentCell As Range
Dim count As Integer
count = 0

For Each currentCell In cRange
    If (iLimits = True And currentCell.Value >= lowB And currentCell.Value <= upB) _
    Or (iLimits = False And currentCell.Value > lowB And currentCell.Value < upB) Then
        count = count + 1
    End If
Next currentCell

COUNTBETWEEN = count
End Function

Count cells between two numbers with SUMPRODUCT

In the SUMPRODUCT-based formula, we will use boolean logic.

Formula:

=SUMPRODUCT((Sales>=3000)*(Sales<=5000))

SUMPRODUCT will return an array with TRUE or FALSE values. We have two criteria, so the result is two arrays:

=Sales>=3000 = {TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE}
=Sales<=5000 = {FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, TRUE, FALSE}

The formula converts boolean TRUE and FALSE values to 0 or 1.

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

If the value meets both criteria, the result is 1. SUMPRODUCT will return the sum of the numbers in the array, 4.

Additional resources and related formulas: