EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel COUNTIFS with Multiple Criteria
Secondary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course

COUNTIFS with Multiple Criteria

By Madhuri ThakurMadhuri Thakur

COUNTIFS with Multiple Criteria 1

COUNTIFS with Multiple Criteria (Table of Contents)

  • Introduction to COUNTIFS with Multiple Criteria
  • How to Use COUNTIFS Function in Excel?

Introduction to COUNTIFS with Multiple Criteria

Excel provides us COUNTIFS function to return a count if certain conditions are met. The function purely works for multiple conditions. This technique is quite useful in complex calculations that involve a huge dataset. COUNTIFS takes multiple parameters as input, but the parameters are of two types only, viz. condition value and range in which condition value is to be searched.

How to Use COUNTIFS Function in Excel?

Now, we will go through a step-by-step procedure to implement and use countifs with multiple criteria in Excel. For this demonstration, we are going to use Volume distributed to various regions of India. Volume is a measure present in the dataset, which has been given by Region, Mode, and Product dimensions. Mode essentially refers to the distribution mode. We’ll use countifs to return the count based on multiple criteria. Let’s go through the following section to understand this.

You can download this COUNTIFS with Multiple Criteria Excel Template here – COUNTIFS with Multiple Criteria Excel Template
  1. First, have a look at the data, which is as shown in the following screenshot. Observe the dimensions and the measure present in the dataset.

Countifs with multiple criteria 1-1

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

  1. As we can see, going through the dataset, we have three dimensions which form three criteria columns. We may want to have a count based on the criteria column is shown above. Now, imagine we want to count for conditions as shown by the following table.

Countifs with multiple criteria 1-2

  1. As we can see above, we want to know the count based on the criteria represented by the two columns. Here, by criteria, to give an example, we essentially mean that count the number of entries for which Region is Ahmedabad, and Mode is M1. This task seems to be difficult, but Excel provides us with a very easy approach through the COUNTIFS function. Let’s first see how the COUNTIFS function works.

Countifs with multiple criteria 1-3

As can be seen in the above screenshot, when we enter the countifs function, Excel automatically shows what parameters need to be passed into the function. The COUNTIFS function takes multiple criteria ranges and corresponding criteria. The first criteria range and criteria appear as criteria range1 and criteria1.

  1. We will use the COUNTIFS function now. For this, let’s pass the input parameters properly. The first parameter is the criterion range. Here, the criteria range is the range in the table in which we want to search for counting. In this case, the first criteria range would be the Range column, and the criterion would be the name of the region which needs to be searched in the range. The criterion has to be selected from the table in which we want the value. The following screenshot shows how the first criterion range and criterion are passed as parameters in the table.

Countifs with multiple criteria 1-4

Do not just forget to make the criteria range constant (append $ sign in the range), as can be seen in the above screenshot.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,452 ratings)
  1. We also have another criterion to be met. This is pertaining to mode. For this, follow the same procedure that we followed for the first criterion except that we would be selecting the Mode column and the criterion range2 from the dataset, and the corresponding value for mode in the other table as the criterion. The implementation is as shown by the following screenshot.

Output 1-5

  1. Now, once done, close the function and press enter. Observe if we get the correct result. As can be seen in the below screenshot, the COUNTIFS function has returned the value 4, which shows that for region Ahmedabad, mode value M1 appears four times. This is correct, as can be seen going through the dataset. Now, let’s implement this function for other region values in the table. For this, copy the function for these entries. Doing this, we get the corresponding count values, as can be seen in the following table.

Output 1-6

  1. So, we find that the COUNTIFSS function makes the seemingly difficult task very easy. The above example we saw had to criteria. Now, we also have a third dimension in the table, which is Product. Now, let’s imagine we want to identify the number of entries with a particular region that used one of the two modes for a certain product. So, this necessarily means that we have three criteria. Well, the implementation of COUNTIFS won’t much differ from what we saw previously. We shall now have third criteria range and corresponding criteria that shall be related to the Product dimension. The table containing criteria is as shown below. We want to return count based on these criteria.

Countifs with multiple 2-1

  1. The following screenshot shows the COUNTIFS function’s implementation to return count based on three conditions, as shown in the above table. Observing the screenshot, we can say that it is just an extended version of the COUNTIFS implementation that we saw previously.

order to return count

  1. Now, when the function is implemented, we get the value 1, which means we have 1 such entry that meets these three criteria. Let’s copy the function across all the desired cells. Doing this, we get the result which is shown by the following table.

Output 2-3

As we can see above, for certain entries, we have got zero. This means that there exist no such entries in the dataset. For the remaining entries, we got value 1, meaning that such combinations are present.

Things to Remember

  • The COUNTIFS function in Excel must be used only when there are two or more criteria. For dealing with a single condition, Excel provides other mechanisms.
  • While implementing the COUNTIFS function, it must be noted that criterion range and criterion are selected from correct sources else the result would be incorrect.

Recommended Articles

This has been a guide to COUNTIFS with Multiple Criteria. Here we discuss How to use COUNTIFS with Multiple Criteria along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Count Formula in Excel
  2. COUNTIFS in Excel
  3. Excel Match Multiple Criteria
  4. SUMIF with Multiple Criteria
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download COUNTIFS with Multiple Criteria Excel Template

EDUCBA

Download COUNTIFS with Multiple Criteria Excel Template

EDUCBA

डाउनलोड COUNTIFS with Multiple Criteria Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more