How to Count Between Two Numbers (COUNTIFS) in Excel (Formula)

In Excel, you can count between two numbers using the COUNTIFS function. With the COUNTIFS function, you can specify an upper limit of the numbers, and a lower limit to create a range of numbers to count. In the following example, we have a list of names with age. Now we need to count the people of two ages i.e., two numbers.

Using COUNTIFS to Count Between two Numbers

You can use the following steps to create formulas using the COUNTIFS to count numbers between 10 and 25.

1. First, enter the “=COUNTIS(“ in cell C1.
   
2. After that, refer to the range from where you want to count the values.
   
3. Next, you need to specify the upper number using greater than and equal sign.
   
4. From here, again you need to refer to the range of numbers in the criteria2.
   
5. In the end, use lower than and equal signs to specify number below than 10 in the counting range.
   

=COUNTIFS(B2:B26,"<=25",B2:B26,">=10")

COUNTIFS can take multiple criteria and count values based on them, and to understand this formula, we need to split it into two parts.

1. First, you have specified the range from which you want to count the numbers and the upper number using the greater than and equal signs.
2. After that, you specified the lower number and the range of numbers.

Using SUMPRODUCT to Count Cells Between Two Numbers

You can also use the SUMPRODUCT function for counting the number of cells between two numbers. In this formula, we need to use the INT function along with the SUMPRODUCT.  See the formula below:

=SUMPRODUCT(INT(B2:B26>=10), INT(B2:B26<=25))

To understand the formula, we need to split it:

In the first INT function, we have used the range of cells from where we want to count the numbers. And >= signs to only refer to the numbers greater than or equal to 10. And it returns an array showing all the numbers above 10 using 1.

And in the second INT function, again you have the same range that considers the numbers lower than and equal to 25.

With both INT functions, you have two different arrays. In the first, you have 1 for each number that is greater than or equal to 10, and in the second, 1 for the values which are lower than or equal to 25.

In the end,

• SUMPRODUCT multiplies each value from the first array with the value from the second array, and you will get an array of values that represents numbers that are between 10 and 25.
• And after that return the sum of those values from the array.