This tutorial describes the formula syntax and usage of the Excel COUNTIFS Function and its usages.
Definition – Excel COUNTIFS Function
Excel COUNTIFS function is a member of COUNT family in Microsoft Excel and advanced version of Excel COUNTIF Function. Excel COUNTIFS function count the number of cells in a data-set or range with a single or multiple of criteria.
It Returns a Value
This function returns a number value
=COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2]…….[criteria_range_n, criteria_n]
Arguments of Excel COUNTIFS Function
range1–[Required] This is the first range where to evaluate the related criteria.
criteria1–[Required] The criteria will use on range 1
range2 – [Optional] this is the second range where to assess the related criteria.
criteria2 – [Optional] the criteria will apply on range 2
Things to Remember
We can apply up to 127 range/criteria pairs in a formula.
This function uses the criteria such as DATES, numbers, text, and other condition.
The non-numeric criteria must be in double quotes.
This function supports logical operators (><, <, <>, =) and wildcards (*,?) for partial matching.
Every additional range must have the same number of rows and columns as the criteria_range1 argument. If the ranges mismatch then we will get a #VALUE! Error.
Use wildcard characters (~ – tilde) as (~?) or (~*) when to find real question mark or asterisk.
Excel COUNTIFS Function with multiple criteria – How it works
Now we will understand Excel COUNTIFS Function with the help of the below image example. We have a data-set where we have multiple records.
Question: How to find the number of persons, who are meeting with all the following criteria or condition?
Sex = “Male”
Salary = “>32000”
Department = “Sales”
We can find the result for the above condition by applying the “Excel Auto Filter” option. Learn how Excel Auto-Filter works.
Excel COUNTIFS Function – Criteria and Range
Now find the solution for the above situation by applying COUNTIFS Function step by step.
Look for the ranges where all of our criteria’s falls within. Such as:-
“Male” criteria fall under “Sex” tab.
“>32000” criteria falls under “Salary” tab.
“Sales” criteria falls under “Department” tab.
Find the range of our each of the condition or criteria in the given data-set. See below:-
- Our first criteria “Male” = falls within Sex tab, range: (B2: B11)
- Second criteria “>32000” = falls within Salary tab, range: (C2: C11)
- Third criteria “Sales” = falls within Department tab, range: (D2: D11)
Know the criteria and is its range. See below:-
Criteria_range1 = Sex (B2:B11)
Criteria1 = Male
Criteria_range2 = Salary (C2:C11)
Criteria_range3 = Department (D2:D11)
Criteria3 = Sales
Put adjust all these criteria ’s and ranges within the Excel COUNTIFS Function.
=COUNTIFS (B2: B11, “Male,” C2: C11, “>32000”, D2: D11,”Sales”) and hit enter.
See the image below:-
Cell G2 has the formula:
In cell G2 we have applied the Excel COUNTIFS Function and see the result.
In place of criteria_range, we can use Excel Name_range Function here. Simply, by name the ranges as follows:
range (B2:B11) = Sex, range (C2:C11) = Salary and range (D2:D11) =Department.
So after giving the name to the ranges, our formula looks like:-
=COUNTIFS (Sex,“Male”, Salary,“>32000”, Department,“Sales”)
Giving the name to a range is called name_range in Excel. In the future blog post, we will learn about what is name_range in Excel and how this works.
Conclusion: – Now I hope after this detailed blog post about Excel COUNTIFS Function, there is no doubt remain in mind. So, if there is any doubt, please feel free to share.