Excel COUNTIFS Function – How to Use (with example)

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

 

Syntax

 

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

 

Excel COUNTIFS Function

 

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)

Criteria2                    =>32000

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

 

Excel COUNTIFS Function

 

Cell G2 has the formula: 

=COUNTIFS(B2:B11,”Male”,C2:C11,”>32000″,D2:D11,”Sales”)

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.

 

 

Related Post

Excel COUNTA Function, Excel COUNTIF Function, Excel COUNTBLANK Function

Leave a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.