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.
- Write the COUNTIFS function in the cell with an opening bracket.
- Select the criteria range by highlighting cells where you want to apply the condition.
Once you have selected the criteria range, add a comma.
- 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.
- Select the criteria range for the second condition and add a comma. The criteria range will be the same as before.
- Type the second condition and add a closing bracket.
Our final formula looks like this:
- Press Enter to apply the formula.
- Copy and paste the formula into other cells and apply the relevant conditions for those cells.