Suppose we have a class of 10 students and each one of them have opted for different subjects as per their choice.
Now, what if we want to find the number of students opting a particular subject in combination with the region he belongs to!!!
Excel COUNTIFS can do the work for us… Let’s see how to do it.
1. What is the COUNTIFS Function?
2. What is the Syntax?
3. How to use it with an example?
1. What is the COUNTIFS Function?
The COUNTIFS function can be used to count the number of cells that match one or multiple criteria.
2. What is the Syntax?
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
- criteria_range1 = (Required) A range of cells on which we have to apply criteria1.
- criteria1 = (Required) Condition based on which the count has to happen on criteria_range1.
- criteria_range2 = (Optional) A range of cells on which we have to apply criteria2.
- criteria2 = (Optional) Condition based on which the count has to happen on criteria_range2.
Note :
- The wildcard characters i.e.? and * can be used in criteria.
- TEXT should be placed in double quotes while the numerics can be passed without quotations.
- Every range that we pass into the formula should have the same number of rows, else it will throw #VALUE error
- If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
3. How to use it with an example?
We have a data set which contains the details of students along with their elected subjects and region they belong to.
Let’s find the count…
1. Finding the count of students who has opted for maths and are from the USA.
2. Finding the count of students whose name contains ‘A’ belongs to INDIA and have scored more than 50.
1. Finding the count of students who has opted for maths and are from the USA
**Explained**
=COUNTIFS(B3:B12,"USA",C3:C12,"Maths")
- B3:B12 = A range of cells on which we have to apply criteria1
- “USA” = Condition based on which the count has to happen on criteria_range1
- C3:C12 = A range of cells on which we have to apply criteria2
- “Maths” = Condition based on which the count has to happen on criteria_range2
2. Finding the count of students whose name contains ‘A’ belongs to INDIA and have scored more than 50.
**Explained**
=COUNTIFS(B3:B12,"India",A3:A12,"*a*",D3:D12,">50")
- B3:B12 = A range of cells on which we have to apply criteria1
- “India” = Condition based on which the count has to happen on criteria_range1, Contains ‘India’
- A3:A12 = A range of cells on which we have to apply criteria2.
- “*a*” = Condition based on which the count has to happen on criteria_range2, Contains ‘A’ letter.
- D3:D12 = A range of cells on which we have to apply criteria3
- “>50” = Condition based on which the count has to happen on criteria_range3, Number greater than 50.
Hope it has helped you…
