COUNTIFS function allows Excel to count numbers that lie between two values.
For this guide, we will use an example of a class mark sheet. We will use the COUNTIFS function to count how many students fall in a particular group depending on their marks.
Counting between two numbers using COUNTIFS
- Select the cell where you want to apply the COUNTIFS function. This cell will display the count of students with marks between the two numbers we enter as our conditions.
Select a cell to enter the COUNTIFS function. - Write the COUNTIFS function in the cell with an opening bracket.
=COUNTIFS(
Write the COUNTIFS function. - Select the criteria range by highlighting cells where you want to apply the condition.
Select criteria range. Once you have selected the criteria range, add a comma.
Add a comma after selecting the criteria range. - Since we are using the COUNTIFS function to count between two numbers, we will apply two conditions. For example, in Group A – we will only consider students with marks above 70 but below 90.Type the first condition in quotation marks. Add a comma once you have entered the condition.
Add the first condition in quotation marks. - Select the criteria range for the second condition and add a comma. The criteria range will be the same as before.
Add second criteria range. - Type the second condition and add a closing bracket.
Add the second condition. Our final formula looks like this:
"=COUNTIFS(B3:B17,">70",B3:B17,"<90")" - Press Enter to apply the formula.
Press Enter to apply the formula. - Copy and paste the formula into other cells and apply the relevant conditions for those cells.
Apply the formula to the rest of the cells.
