COUNTIF Multiple Conditions (Table of Contents)
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:
Suppose we have data of tennis players as shown below:
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(“.
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.
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.
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 (“+”).
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.”
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:
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.
Step 1: In cell E2, start initiating the SUM function by typing “=SUM(“.
Step 2: Within the SUM function, use the COUNTIF function and initiate the same by typing “COUNTIF(“.
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.
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.
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.
Step 6: Close the curly braces for the criteria and the round braces associated with both COUNTIF and SUM functions to complete the formula.
Press Enter key to see the output of the formula we created.
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 –
