In this tutorial, we will learn how to count cells with multiple conditions or criteria across multiple ranges of cells. This function is very similar to COUNTIF function, which is only valid for a single range of cell. Microsoft Excel 2021 is used.
Syntax of COUNTIFS
=COUNTIFS([cell_range1, criteria1], [cell_range2. criteria2] ...)
Each cell range can have its own criteria or condition.
- Cell range is the range on which the criteria will be applied, such as B2:B11.
- Criteria can be a condition which needs to be fulfilled in order to count the cell. Criteria can be an arithmetic condition or plain text.
It is important to note that all cell ranges should have equal number of rows and columns.
Using COUNTIFS to count Students.
In this tutorial, we will be using COUNTIFS to count the number of students who have attained more than 80 marks in all subjects. Students who have less than 80 marks in any subject will not be counted.
We have data of 10 students who have secured different marks in each subject.
Enter the formula in the cell where you want the result. =COUNTIFS(
It is necessary to put = before any formula.
Next enter the cell range in the formula: =COUNTIFS(B2:B11
And then enter the criteria, in this case, we want to count those students who have more than 80 marks. This arithmetic condition will be enclosed in double quotation marks: “>80“.
Put comma, after the cell range. The formula becomes: = COUNTIFS(B2:B11, ">80".
Similarly, do the same for other subjects.
We have entered the same criteria, for every cell range.
The formula becomes: =COUNTIFS(B3:B12,">80",C3:C12, ">80",D3:D12,">80")
As it can be seen that we have three students who have attained more than 80 marks in all subjects.
Similarly, we can count the number of students who have more than 70% marks in all subjects.
So, the final result will be:
More about counting cells: