# 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.

#### 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:-