This tutorial describes the formula syntax and usage of the Excel COUNTIFS Function and its usages.
Excel COUNTIFS Function – Definition
Excel COUNTIFS function is a member of the COUNT family and advance version of Excel COUNTIF Function.
This function is a combination of two functions, “COUNT” and “IFS” two functions. Separately both of them cannot do much but together can do wonders.
This function returns a number value as a result.
Syntax of this function
=COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…….[criteria_range_n, criteria_n]
Arguments
criteria_range1–[Required] This is the first range where to evaluate the associated criteria.
criteria1–[Required] The criteria will use on range 1.
criteria_range2 – [Optional] This is the second range were to evaluate the associated criteria.
criteria2 – [Optional] the criteria will use on range 2
Things to Remember
We can apply up to 127 range/criteria pairs in a formula.
Excel COUNTIFS Function use the criteria such as DATES, numbers, text, and other condition.
The non-numeric or text criteria must be in double-quotes.
This function supports logical operators (><, <, <>, =) and wildcards (*,?) for partial matching
If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
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
Here is an image, where we have a dataset and on the right side of this image we have some condition that needs to be applied to find the result.
Excel COUNTIFS Function
Situation
How to find the number of persons those who are meeting with all the following criteria or condition in the above data-set.
Sex = “Male”
Salary = “>32000”
Department = “Sales”
Now find the solution for the above situation by applying Excel COUNTIFS Function step by step.
Look for the ranges where all of our criteria fall within. Such as:-
“Male” criteria fall under the “Sex” tab.
“>32000” criteria falls under “Salary” tab.
“Sales” criteria fall under the “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)
Now know the criteria and is its range. See below:-
Criteria_range1 = Sex (B2:B11)
Criteria1 = Male
Criteria_range2 = Salary (C2:C11)
Criteria2 =>32000
Criteria_range3 = Department (D2:D11)
Criteria3 = Sales
Put all these criteria within the Excel COUNTIFS Function.
=COUNTIFS (B2: B11, “Male”, C2: C11, “>32000”, D2: D11,” Sales”) and hit enter.
See the image below:-
Excel COUNTIFS Function
In cell G2 we have applied the Excel COUNTIFS Function and see the result.
We can also use the name in place of ranges, such as range (B2: B11) =Sex), range (C2: C11) =Salary, and range (D2: D11) =Sales.
So after giving the name for the ranges, our formula looks like as follows:-
=COUNTIFS (Sex, “Male”, Salary,“>32000”, Department, “Sales”)
Giving a name to a range is called name_range in Excel. In the future blog post, we will learn about name_range in Excel.
I hope you find this post about ‘Excel COUNTIFS Function’ useful.
Furthermore, details for the Excel COUNTIFS Function are provided on the Microsoft Office website.
Please feel free to put your comments or suggestion in the comment box below and if you like this post then do not hesitate to share it.
Thanks
Another Useful Post