EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel COUNTIF Multiple Conditions
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

COUNTIF Multiple Conditions

COUNTIF Multiple Conditions

COUNTIF Multiple Conditions (Table of Contents)

  • What is COUNTIF Function?
  • Examples of COUNTIF Multiple Conditions

What is COUNTIF Function?

COUNTIF Function counts the number of cells that match certain criteria under a given number of cells. We also know that to deal with multiple conditions, we have the COUNTIFS function in Excel, which allows us to slice the data based on multiple conditions. This function counts the number of cells based on more than one criteria. We all know that COUNTIF can’t compare two or more conditions simultaneously to get the desired count. However, there are several ways to use the COUNTIF function in a more versatile manner to give us the count of cells based on multiple criteria like COUNTIFS. We also could use COUNTIF with the combination of other Excel functions such as SUM, AVERAGE, etc. In this article, we are about to see how we can use the multiple conditions under COUNTIF itself rather than moving towards COUNTIFS.

Examples of COUNTIF Multiple Conditions

Lets us discuss the Examples of COUNTIF Multiple Conditions.

Example #1 – COUNTIF Function with Multiple Conditions

Since we can use one condition at a time under the COUNTIF function, we move to COUNTIFS when it comes to counting the number of cells based on multiple criteria. However, if we use the COUNTIF function with some versatility, we can work it out with multiple conditions. See the example below:

Start Your Free Excel Course

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

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)

Suppose we have data of tennis players as shown below:

COUNTIF Multiple Condion 1-1

We wanted to count the number of cells that have “Rafael Nadal” and “Roger Federer” together in the entire data that varies across cells A1 to C5.

Step 1: Start initiating a COUNTIF function under cell E2 by typing “=COUNTIF(“.

COUNTIF Multiple Condion 1-2

Step 2: The first argument that is required for this function is “range.” Use the range as A1:C5 so that we can check the entire data range.

COUNTIF Multiple Condion 1-3

Step 3: Now, as criteria, use “Rafael Nadal” so that it can capture the count of cells that have “Rafael Nadal” in it. Close the bracket to complete this formula.

COUNTIF Multiple Condion 1-4

Step 4: Now, we need to add one more COUNTIF condition for “Roger Federer.” We can combine one with the help of a plus sign through the keyboard. To add a new COUNTIF, use plus sign (“+”).

COUNTIF Multiple Condion 1-5

Step 5: Now, use the same format of the COUNTIF function which we used in the previous three steps. However, this time under the criteria, it uses “Roger Federer” instead of “Rafael Nadal.”

COUNTIF Multiple Condion 1-6

Step 6: Press the Enter key to see the output of the function we have just created. You can see an output as shown below:

COUNTIF Multiple Condion 1-7

If you could see the blue-colored cells, those are the ones that have “Roger Federer” and “Rafael Nadal” in them, and the count of those cells is nine, which is rightly captured under cell E2. This is how we can apply multiple conditions in Excel using COUNTIF.

Example #2 – COUNTIF and SUM Function Together

Well, our first example is not the exact way of adding multiple conditions under the COUNTIF function. The reason, you ask? Because we had to use two separate COUNTIF functions with the help of an addition operator to get the result. In this example, we will see how we can use the SUM function with COUNTIF to add the multiple conditions and get the desired result.

The data is the same as it is used in the previous Example.

SUM function together 2-1

Step 1: In cell E2, start initiating the SUM function by typing “=SUM(“.

SUM of Together 2-2

Step 2: Within the SUM function, use the COUNTIF function and initiate the same by typing “COUNTIF(“.

SUM of Together 2-3

Step 3: Under COUNTIF, firstly, you need to specify the criteria (range) as A1 to C5. Since that is the range within which we wanted to count the desired texts, you need to make the reference absolute. Do the same by pressing the keyboard F4 key.

SUM of Together 2-4

Step 4: Now, to add the multiple conditions here in a single COUNTIF, we are going to use curly braces. Open a one with an opening curly brace within COUNTIF in the criteria section.

SUM of Together 2-5

Step 5: Within these curly braces, add two of the criteria, “Rafael Nadal” and “Roger Federer.” This way, we add multiple criteria under the same COUNTIF function. Since both these values are text, you need to enclose those within quotes. Also, it would help if you used a comma as a separator under curly braces to separate the two criteria.

SUM of Together 2-6

Step 6: Close the curly braces for the criteria and the round braces associated with both COUNTIF and SUM functions to complete the formula.

SUM of Together 2-7

Press Enter key to see the output of the formula we created.

SUM of Together 2-8

We have count as nine throughout the data for both the criteria. Well, if you think this formula works, here is an explanation for you. The COUNTIF function nested under SUM counts two different conditions under a given range and gives two counts specifically. 5 for Roger Federer and 4 for Rafael Nadal. These two count values are now input for the SUM function, which simply sums up 5 and 4 to give us the output as 9. Remember that we need to use the SUM function to get the result that gives a combined count of both criteria values.

This is how we can add multiple conditions under the COUNTIF function, and it works fine like COUNTIFS. This article ends here. Let’s wrap the things up with some points to remember.

Things to Remember About COUNTIF Multiple Conditions

  • Ideally, COUNTIF is structured to work on one criterion at a time. However, with some modifications, you can also make it work under multiple conditions.
  • The best way to use COUNTIF for multiple conditions is by combining it with the SUM function.
  • While using COUNTIF and SUM together, you need to specify multiple conditions under curly braces with a comma as a separator. This trick works on both data types, numeric as well as text.

Recommended Articles

This has been a guide to COUNTIF Multiple Conditions. Here we discuss How to use COUNTIF Multiple Conditions along with practical examples. You can also go through our other suggested articles –

  1. Worksheets in Excel
  2. Time Function in Excel
  3. Percent Change in Excel
  4. Excel Reverse Order
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for Marketing Training (8 Courses, 13+ Projects)4.9
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.

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