In this article we will learn how to count the number of values between two specified values in a list in Microsoft Excel.
When you work with reports and dashboards, you are often required to count values b/w a given range. You can use filters to count, but that's not feasible while working with large amounts of data. I mean you don’t want to apply filters on each column again and again to update your report. Right?
Don’t worry, COUNTIFS function will rescue you from all the hassle.
COUNTIFS(Criteria Range1, Criteria, Criteria Range2, Criteria,.....)
Let's say, in a sheet you have a list of students in one column and their marks adjacent to that column. Now you want to lookup all values in excel and count the number of students who scored between:
|=COUNTIFS( SCORE RANGE,”>=START SCORE”, SCORE RANGE, “<=END SCORE” )|
The COUNTIFS function of Excel 2016 is that it can handle multiple conditions. In our case, we have only two conditions, the Start Score and End Score.
To do that we need to Identify all the attributes.
SCORE RANGE: It is the column range that contains marks. That is C3:C10.
START SCORE: The value from which you want to start your account. Or say the lower bound of your range. That is 41.
END SCORE: The value to which you want to count. In other words, the upper bound of your range. That is 60.
In Cell F3, write this formula:
Hit the enter key. You will have your answer. In this scenario, it is 4. There are four students who scored between 41 and 60.
COUNTIFS can take multiple conditions. It can take more conditions than anyone will ever need, but to be precise, it can handle 127 pairs of ranges and conditions.
Here we have only two conditions, the lower bound and upper bound.
$C$3:$C$10: So marks are in C3:C10, it means it is our criteria range. We have used $ before to lock the references. So that it doesn’t change when we copy our formula.
”>=41”: The Lower bound is 41. We used greater than or equal to (>=) sign before 41 so that it includes 41 in our count. Note that we used double quarts for criteria here. This is because we entered the criteria manually.
$C$3:$C$10: The second criteria range is the same because we want to count from the same column.
”<=60”: The Upper bound is 60. Since we want to include 60 in our result, we used less than or equal to (<=) sign. Now excel will check if a value exists in the data in given range it will count it.
In the above example, the formula is hardcoded. If you want add one more range then you need some changes in each formula.
For example, if you enter 100 new conditions then you will need to make 100*2 Changes (Lower bound and Upper Bound). That is why we try not to hard code it.
Now we want a formula that counts the number of values between two specified values that is dynamic. We don’t want to make any changes to the new criteria.
|=COUNTIFS( SCORE RANGE,”>=”&START SCORE CELL, SCORE RANGE, “<=”&END SCORE CELL)|
SCORE RANGE: It is the column range that contains the score. Here it is C3:C10
“>=”: The greater than or equal to sign for checking the values who are greater than or equal to Lower bound.
&: This is very important as it concatenates the cell value with your conditions. “>=”&F3 will translate to “>=41”.
Lower Bound Cell: This is the cell address that contains your lower bound.
Upper Bound Cell: This is the cell address that contains your Upper bound.
In Cell H4, write this
Hit enter and you have your results.
Whenever you change to the upper or lower bound, the count will update itself automatically.
Personally, I have used COUNTIFS a lot. For dashboarding, automating reports and many more things. It saves a lot of time. There are many other ways you can use COUNTIFS for doing the same thing.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.