In Excel, we can count numbers by range using COUNTIFS() function.
What is COUNTIFS?
The purpose of COUNTIFS() function is to apply certain criteria to cells across multiple ranges and return the count of the times a criterion is met.
A formula to Count numbers by range with COUNTIFS
=COUNTIFS(range, “>=low”,range, “<=high”)
The COUNTIFS() will be applied on the cells, defined in the argument “range”. >=low and <=high will give the lower and upper bound respectively. Meaning, COUNTIFS() will be looking for the cells whose value falls between upper and lower bound.
Let us work on an example to understand it a little better.
How to use the Formula to check how many students fall in a particular marks range?
Figure 1. Example 1 of how to count numbers by range with COUNTIFS()
In this example, you have a small table containing students’ marks information. Our purpose is to find how many students fall in different marks ranges defined in the “Marks Range and count table” To count numbers by range with COUNTIFS() on this table:
- Select any column, where you want to display the result for counting numbers by range. In this case, it is I6.
- In the function box, type COUNTIFS() FUNCTION.
- Give the arguments i.e. the range. For I6, assign the range E6:E60. The lower bound is 40 and upper bound is 49 i.e. COUNTIFS(range, “>=low”,range, “<=high”)
- So, this formula will go through Marks column and return the total count of cells, which fall in the range of 40-49. In this case, the count came out to be 10.
- Similarly, for other mark ranges, you can look at the Marks Range and Count Table.