How to Use Excel COUNTIFS Function with Multiple Criteria

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.

Image for Excel COUNTIFS Function
Image 01

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
Image 02

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. Please feel free to share any doubts if you have.

Excel COUNTIF Function, Excel COUNTBLANK Function, Custom AutoFill List, Excel COUNTA Function

close

Leave a Comment

Your email address will not be published. Required fields are marked *