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.
- First, enter the “=COUNTIS(“ in cell C1.
- After that, refer to the range from where you want to count the values.
- Next, you need to specify the upper number using greater than and equal sign.
- From here, again you need to refer to the range of numbers in the criteria2.
- 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. To understand this formula, we need to split it into two parts.
- B2:B26 – This is the range of cells that the formula is evaluating. In this case, it’s checking each cell from B2 to B26.
- Criteria 1: “<=25” – This is the first condition applied to the cells in the range B2:B26. It tells Excel to consider only the cells where the value is less than or equal to 25.
- Criteria 2: “>=10” – This is the second condition applied to the cells in the same range. It instructs Excel to consider only the cells with a value greater than or equal to 10.
This formula will scan through each cell from B2 to B26 and count the number of cells containing a value between 10 and 25. The result will be the total number of cells between this range.
Using COUNTIF Function
You can also use a formula using the COUNTIF function. In this formula:
This formula is divided into two parts using the COUNTIF function.
- The first part,
COUNTIF(B2:B26,">=10"), counts the number of cells in the range B2 through B26 with a value greater than or equal to 10. - The second part,
COUNTIF(B2:B26,">25"), counts the number of cells in the same range (B2 through B26) with a value greater than 25. - The
-operator in between these two parts subtracts the count of cells with a value greater than 25 from the count of cells with a value greater than or equal to 10.
So, this formula’s result will give you the number of cells in the range B2 to B26 that have a value greater than or equal to 10 but less than or equal to 25.
Note – In Excel, criteria within functions like COUNTIF and COUNTIFS are placed inside double quotes to tell Excel to interpret them as text, including any operators like >, <, or =. This ensures that the formula understands what you want to compare, like “greater than or equal to 10” or “less than or equal to 25,”.
Using SUMPRODUCT to Count Cells Between Two Numbers
You can also use the SUMPRODUCT function to count the number of cells between two numbers. In this formula, we must use the INT function and the SUMPRODUCT. See the formula below:
=SUMPRODUCT(INT(B2:B26>=10), INT(B2:B26<=25))
- SUMPRODUCT – The SUMPRODUCT function multiplies corresponding values in given arrays or ranges and then sums up all those products. It’s like doing a little bit of multiplication and addition simultaneously.
- INT – The INT function in Excel rounds down a number to the nearest integer. This means it removes any decimal part of a number and leaves just the whole number part.
To understand the formula, we need to split it:
The INT function in Excel converts a value to an integer. When combined with a condition, such as B2:B26>=10, it returns an array of 1s and 0s. A 1 represents True, meaning the condition is met, and a 0 represents False, meaning the condition is not met.
Here, INT(B2:B26>=10) generates an array based on whether the values in cells B2 to B26 are greater than or equal to 10.
Similarly, INT(B2:B26<=25) generates an array based on whether the values in cells B2 to B26 are less than or 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, the SUMPRODUCT function in Excel multiplies corresponding components in the given arrays and returns the sum of those products. When applied to the two arrays generated by INT(B2:B26>=10) and INT(B2:B26<=25), it multiplies each pair of corresponding elements together and sums the result. This counts the number of cells where both conditions (values greater than or equal to 10 and less than or equal to 25) are true.
Which Formula is Better
Each of these formulas is designed to count the values in the range B2:B26 that fall between 10 and 25, inclusive, but they achieve the result using different approaches:
- COUNTIFS(B2:B26,”<=25″,B2:B26,”>=10″) – This is a more suitable and accurate formula. It uses COUNTIFS, which allows for multiple criteria within the same function. This formula counts only the cells in the range B2:B26 where the values are greater than or equal to 10 and less than or equal to 25.
- COUNTIF(B2:B26,”>=10″)-COUNTIF(B2:B26,”>25″) – This formula correctly counts values from 10 to 25. It does so by subtracting the count of all values greater than 25 from those greater than or equal to 10. This subtraction effectively excludes any values above 25, thus isolating the count between 10 and 25.
- SUMPRODUCT(INT(B2:B26>=10), INT(B2:B26<=25)) – This formula uses SUMPRODUCT to count values within the same range. It multiplies two arrays: one that results from checking if each value is greater than or equal to 10 and another if each value is less than or equal to 25. The product array will have 1s for accurate matches and 0s; otherwise, entries that meet both criteria will be effectively counted.
The =COUNTIFS(B2:B26,”<=25″,B2:B26,”>=10″) formula is typically the best choice for this scenario. COUNTIFS is generally faster and more resource-efficient than SUMPRODUCT, especially for larger or more complex data, as it doesn’t require creating intermediate arrays or performing multiple operations.