Countif with Multiple Criteria: A Comprehensive Guide 3 Ways

When you use Excel for data analysis jobs, COUNTIF with Multiple Criteria can be your daily buddy to help you. Because this complex yet flexible formula counts the number of cells when you impose rules and conditions. In many cases, the criteria can be like simple expression or comparison operator. However, there are scenarios where we need multiple criteria to work simultaneously.

What is Countif? Learning excel countif multiple criteria

Countif is an Excel function will count the cells in a range if they meet your certain condition. The syntax for Countif is simple like below thing.

Where range is the all cells that we want to count. While criteria is the condition we want to apply.

countif multiple conditions

For example, in case you have a list of numbers in cells A1 to A10. And we want to count cells that are greater than 5. We can use the following Countif formula.

This will give us the number in range A1 to A10 that are greater than 5.

Using COUNTIF with Multiple Criteria

What if we want to count cells that meet multiple criteria? For example, let’s say we have a table with columns for Product, Region and Sales. We want to count the number of sales for a specific product in a specific region.

If you wanna do it, we can use the COUNTIFS function. Because it can specify multiple criteria. The syntax for COUNTIFS when you have multiple condition is below.

Syntax Breakdown

• criteria_range1: The range we want to apply the first criteria to.
• criteria1: The criteria we want to use to count cells in criteria_range1.
• [criteria_range2]: This is Optional. You can choose another cell group to apply the second criteria to.
• [criteria2]: Again, this is optional if we have criteria we want to use to count cells in criteria_range2.

We can add as many criteria_range and criteria pairs.

Using COUNTIFS in Practice

Let’s use the example of counting sales for a specific product in a specific region. We have a table with columns A, B and C. The table is containing data on products, regions and sales, respectively. We want to count the number of sales for the product “Widget” in the region “East.”

This formula counts cells in column C that meet the criteria “Widget” in column A and East in column B.

We can add more criteria pairs if we need. For example, if we wanted to count the number of sales for the product Widget in the region East. And those will be greater than 1000.

This formula counts the cells in column C that meet all three criteria. If we summarize, these 3 criteria are Widget in column A, Eas in column B and a value greater than 1000 in column C.

countifs function in excel with multiple criteria: countif 2 conditions

Countif can also work to count the number of cells meeting multiple criteria simultaneously. If we wanna do this, we can use the following syntax.

For example, we can look at the same example with Product, Region and Sales Amount. We want to count the number of sales. Here, the Product is Widget and the Region is either East or West. We can use the following Countifs formula:

This will count sales where that meet all of above conditions you write in formula.

Using Wildcards with countif with multiple conditions

Countif can also operate even if you use wildcard characters to count cells to match a pattern. The asterisk (*) can match any number of characters. While the question mark (?) can be match any single character.

For example, let’s say we have a list of names. And we want to count the names that start with the letter J. We can use the following Countif formula:

This will count names in the range A1 to A10 that start with the letter J.

Conclusion on multiple countif conditions

Countif is a powerful function in Excel. And as we saw in above examples, you can complex your formula adding 2 or 3 conditions. When you set up your formula correctly, you can even add up to 127 conditions in one single command.

How to Countif Multiple Criteria?

When we need to count cells in line with multiple criteria simultaneously, we can use the Countifs function. In this case, wildcard characters with Countif can count cells to find a pattern. When you apply these techniques, you will definitely can save time when working with data in Excel.

For more excel related topics, you can check our most valuable resources here. Countifs formula is a great start for advancing your knowledge in Excel miracles. When you got it, you can look at applying macros for example.

FAQs

Can we say the Countif work with dates?

Of course it can work with dates. But here, you should specify the date in the criteria argument. And another important point using the appropriate date format.

Q2. How many criteria to apply with Countifs?

A2. Countifs can handle up to 127 criteria pairs.