WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » COUNTIFS Function in Excel

COUNTIFS Function in Excel

What is the COUNTIFS Function in Excel?

Excel “COUNTIFS” function counts the supplied range of values based on more than one criterion. In our earlier article, we have discussed how to use the “COUNTIF” function. “COUNTIF” function counts based on single criteria.

For example, look at the below data for the sample.

Countifs Function in Excel Example

In the above data, if we want to count how many products of “B” are there in the region “East.” Here we not only need to count only product “B” but also in the region “East.”

This is where the COUNTIFS function helps us to count based on multiple conditions.

Countifs Function in Excel

Syntax

Let’s look at the COUNTIFS formula in excel.

Syntax

  • Criteria Range 1: What is the first range we need to count?
  • Criteria 1: What do we need to count from the criteria range 1?
  • Criteria Range 2: What is the second range we need to count?
  • Criteria 2: What do we need to count from the criteria range 2?

Like this, we can supply up to 127 range or criteria’s combination in these formulas. Now we will see examples of the COUNTIFS function in excel.

How to Use COUNTIFS Function with Multiple Criteria in Excel?

Below are some of the examples of COUNTIFS formula in excel.

You can download this COUNTIFS Function Excel Template here – COUNTIFS Function Excel Template

Example #1

Let’s have a look at the simple example of COUNTIF formula with excel. For this example, look at the below data.

Countifs Function in Excel Example

From the above list, we need to count the product “B” in the region “East.”

Let’s open the COUNTIFS formula first.

Example 0.1

Criteria Range 1 is the first range of cells that we need to count; let’s select Region first.

Note: You can select the Product also.

Countifs Function in Excel Example 0.1.1

The next argument is Criteria 1, so in the select range of cells (Criteria Range 1), what do we need to count??? i.e., “East.”

Example 0.1.2

The third criteria are Criteria Range 2 for this select product range of cells.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Countifs Function in Excel Example 0.1.3

The next argument is Criteria 2, i.e., what do we need to count in the selected Criteria Range 2. In this case, we need to count “B.”

Example 0.1.4

Ok, that’s all.

Hit the enter key; we will have the count of Product “B” for the region “East.”

Countifs Function in Excel Example 0.1.5

So, for the region “East,” we have 2 product “B” count.

Even though we have another count of Product “B” in cell B12, the formula has ignored this because the region for that product is “West,” not “East.”

Example #2

For the previous example data, I have added one extra column, i.e., Sales Person. From this table, we need to count what is the total count of product “B” in the region “East” for the salesperson “Ranya.”

For this example, look at the below data.

Example 1

Here we need to look at three criteria’s which one more than the previous example. So with the continuation of the formula from the above example.

Countifs Function in Excel Example 1.1

For the Criteria Range 3, select the range as “Sales Person” cells.

Example 1.2

In the Criteria 3, we need to count for the sale person “Karan.”

Countifs Function in Excel Example 1.3

Ok, that’s all; we will have a count for the salesperson “Karan” for the region “East” for the product “B.”

Example 1.4

We have a count of product “B” for the region “East” is two, but for the salesperson “Karan,” this is only 1, so our formula has the same count.

Example #3 – COUNTIFS with Logical Operators

We can use logical operators with the COUTNIFS formula to structure our criteria. Logical operators in excel are Greater than (>), less than (<), and equal to (=).

For this example, look at the below data.

Countifs Function in Excel Example 2

From this data, we need to get the count of products in the region “East” if the Price is greater than 20.

Open COUNTIFS formula.

Example 2.1

Select the first Criteria range, one as the region column.

Countifs Function in Excel Example 2.2

Mention the criteria as “East.”

Example 2.3

Next, select the Price column as Criteria Range 2.

Countifs Function in Excel Example 2.4.0

For this range, our criteria are two counts if the price is >20. So mention the criteria with double quotes as “>20”.

Example 2.5

That’s all hit enter key; we will have a count of products where the price is >20 for the region “East.”

Countifs Function in Excel Example 2.6

Example #4

Let’s look at one more example, where we can use two logical operator symbols. For this example, look at the below data structure.

Example.3

In this data, we need to count how many invoices are sent between 20-Jun-2019 to 26-Jun-2019.

Countifs Function in Excel Example.3.1

Open COUNTIFS function.

Example.3.2

Select the Criteria Range 1 as an Invoice Date column.

Countifs Function in Excel Example.3.2.5

Now the criteria are we need to count invoices between 20-Jun-2019 to 26-Jun-2019. So, mention the greater than symbol (>).

Example.3.3

Since we have already had the date in a cell, put the ampersand symbol, and select the D2 cell.

Countifs Function in Excel Example.3.4.0

Now again for Criteria range 2, select the range as Invoice Date only.

Countifs Function in Excel Example.3.5.0

This time we need the count of invoices below the date of 26-Jun-2019. So, mention less than a symbol in double-quotes and select D3 cells.

Countifs Function in Excel Example.3.6

So invoices sent between 20-Jun-2019 to 26-Jun-2019 is 3.

Example.3.7

Like this, we can use the COUNTIFS function in excel to count based on several applied criteria.

Recommended Articles

This has been a guide to COUNTIFS Function in Excel. Here we discuss how to use the COUNTIFS formula in excel with multiple criteria along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Countif not Blank in Excel
  • Formula of COUNTIF in Excel
  • COUNTIF Top Examples
  • COUNTIF with Multiple Criteria
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download COUNTIFS Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More