How to Use Excel COUNTIF Function with Multiple Criteria in Different Columns

Excel COUNTIF function searches for a single criteria in a given range and returns the total number of occurrences of that criteria in the range.

But when you have to meet multiple criteria in different columns, COUNTIF alone cannot help you. However, it’s possible to use this function with multiple conditions in different columns with the help of some additional functions.

In this Excel tutorial, you will learn how to use the COUNTIF function with multiple criteria in different columns.

Let’s consider the following simple dataset. Here you see just two columns: column B has some brand names and column C has their respective product names.

Say you want to count how many times Asus Desktop occurs in this data. So, we have 2 criteria here, criteria-1: the brand name, Asus, and criteria-2: It’s a Desktop.

Overview of Using Excel COUNTIF with Helper Column Based on Multiple Criteria in Different Columns

As COUNTIF cannot work with multiple criteria, we have to use a helper column. Look at the image above.

Here, the helper column in D uses the AND function and helps to identify which brand-product pair is TRUE for our set criteria (Asus Desktop). Then COUNTIF returns the TRUE count, which is 3, i.e. Asus desktops occur thrice in the data.

To create the helper column, you can use functions like AND, OR, or, IFS based on criteria types.

If you want to use a single Excel function to count based on multiple criteria in different columns, COUNTIFS is an excellent alternative.

The SUM and SUMPRODUCT functions are other alternatives to do the same task.

How to Use Excel COUNTIF Function with Multiple Criteria in Different Columns

In this part, we shall count based on multiple criteria in different columns using the Excel COUNTIF function. To match multiple conditions, we need to create a helper column first since COUNTIF alone cannot do that.

Case 1: Matching 2 AND Type Criteria (HP Desktops) in Different Columns

Let’s say, I have the following sales dataset that contains Month, Brand, Product, Color, and Units Sold (see the image below).

In the first case, we shall count how many times HP Desktop occurs in the sales data.

At first, I’ll create a helper column using the AND function. This function will return TRUE where the criteria match.

Then, I’ll use the COUNTIF function to count TRUE from the helper column to count HP Desktop names.

Follow these steps:

Step 1: Add a helper column in column G.

Step 2: My criteria are HP and Desktop. The AND formula in the helper column will return TRUE when both are true in the same row, else it will return FALSE.

Now, input this formula in cell G7:

Step 3: Press Enter and you will get the following output.

Now, hover your mouse over the bottom right corner of cell G7, and you will find the Fill Handle icon.

Step 4: Double-click on the Fill Handle icon and this will copy the formula for the rest of the cells in the helper column.

Step 5: Now, in cell C28, I will count TRUE in the G7:G21 range. So, I input this formula in cell C28:

Step 6: Press the Enter key on your keyboard and you will find the following output.

So, HP Desktop occurs twice in the sales data.

Case 2: Matching 3 AND Type Criteria (Black HP Desktops) in Different Columns

What to do if you have 3 criteria to match and return the count with the COUNTIF function? In this example, I will explain this.

Let’s say this time the criteria are brand = HP, product = Desktop, and color = Black.

We have to count the occurrences where all these 3 conditions are met, i.e. how many times Black HP Desktop occurs in the sales data.

I’ll use the same AND function in the helper column to solve this. I have to add just one more logical condition for the extra 3rd criterion (color = Black).

Follow these steps:

Step 1: In cell G7, input this formula:

Here, E7=$C$27 is the 3rd condition added for color=Black criteria.

Step 2:

After pressing Enter and copying the formula down, we see the following result.

Step 3: Now, just like case 1, I will count TRUE in the G7:G21 range with the formula below.

You’ll see that Black HP Desktop occurs 2 times.

Case 3: How Many Times HP or Lenovo Desktop Products Are Sold? (AND-OR Criteria Combination)

You may want to know how many times HP or Lenovo Desktop are sold. This will be an example of an AND-OR criteria combination.

To solve this, I will use the OR and the AND functions to create the helper column. After that, the COUNTIF function will come into action.

The formula inside the helper column will be like this:

=OR(AND(brand_cell=HP,product_cell=Desktop),AND(brand_cell=Lenovo,product_cell=Desktop))

If any of the AND part of this formula returns TRUE, OR will return TRUE.

Now, follow these steps:

Step 1: In cell G7, input this formula:

Step 2: Press Enter and then copy the formula down to all cells.

Step 3: Now again use the COUNTIF formula as before.

The HP or Lenovo Desktop occurs 4 times in the dataset.

Read More: How to apply COUNTIF with multiple OR criteria in Excel

Case 4: How Many Times HP Desktops Sold More Than 40 Units?

Suppose I want to know how many times more than 40 units of HP Desktops are sold.

So, the criteria are brand = HP, product = Desktop, and Units sold > 40.

I can solve this very easily if I use the following formula to create a helper column in column G.

Then I will count the TRUE results in that column using the following formula:

You’ll see that more than 40 units of HP Desktops are sold 2 times.

Modification to Helper Column Formula for Greater/ Less Than or Equal to Criteria:

You don’t have to look back to the helper column formula if you follow this guide. The upcoming formula can handle any criteria you specify in cell C27: >, <, <=, >= or =.

I’ll use the same AND function in the helper column here.

But now I will divide the third criterion into 2 segments- one is greater than operator (>); another is 40 (or any value you put as criteria).

In this case, we will use the IFS function along with AND to create the helper column.

Now, put the following formula in cell G7, then press Enter, and then copy the formula down to all cells:

Put the following formula in cell C29 to count TRUE in the G7:G21 range:

So, HP Desktops are sold more than 40 units 2 times.

And, you can change the operator in cell C27 at any time and get the appropriate count in cell C30.

Alternative #1: Using COUNTIFS Function with Multiple Criteria in Different Columns Instead of COUNTIF

Do you use Excel 2010 or more updated versions? Then I will recommend using the COUNTIFS function instead, to count based on multiple criteria in different columns. Because this function is dedicated to counting based on multiple conditions in different (or the same) columns. So, you don’t need to create any helper columns like the above discussion.

Now, in the following examples, I will show you how to solve the same problems of Cases 1 to 4 above using a simple COUNTIFS formula.

Case 1: Matching 2 AND Type Criteria (HP Desktops) in Different Columns

Here, criteria 1: HP and criteria 2: Desktop. To count how many times you will find HP desktops in your sales data, use the following formula in cell C28.

Case 2: Matching 3 AND Type Criteria (Black HP Desktops) in Different Columns

This case deals with one more AND criterion- Black color. So you just have to modify the previous COUNITFS formula a bit to add that criteria.

Case 3: How Many Times HP or Lenovo Desktop Products Are Sold? (AND-OR Criteria Combination)

COUNTIFS works for multiple criteria, but sadly it cannot handle AND-OR criteria combination solely.

But it’s possible to count based on AND-OR criteria in different columns if you add 2 COUNTIFS functions.

Write the following formula in cell C29:

Here,

COUNTIFS(C7:C21,C25,D7:D21,C27) part returns count of HP Desktops.

And COUNTIFS(C7:C21,C26,D7:D21,C27) part returns the count of Lenovo Desktops.

The plus operator works for OR criteria.

Case 4: How Many Times HP Desktops Sold More Than 40 Units?

Here, the criteria are brand = HP, product = Desktop, and Units sold > 40.

Consider the third criterion in 2 segments. You can simply join these 2 segments with the Ampersand operator (&) into the COUNTIFS function.

So, put this formula in cell C30:

Alternative #2: Using SUMPRODUCT Function in Excel 2007 or Later Versions

You can also use the SUMPRODUCT function instead if you use Excel 2007 or later versions. This function also can handle multiple criteria in different (or same) columns.

You can apply this function in all the above-mentioned cases.

For example, in the 1st case, the SUMPRODUCT formula will be like the following:

For other cases, use the following formulas:

In Case 2:

In Case 3:

In Case 4:

Read More: SUMPRODUCT and COUNTIF functions with multiple criteria

Alternative #3: Use SUM Function Instead of COUNTIF with Helper Column (Available in All Excel Versions)

The SUM function can be the best alternative to COUNTIF in all 4 cases because of its availability in all Excel versions and it can work without any helper column.

You have to just replace the SUMPRODUCT function of the previous examples with the SUM function.

For Case 1, the formula is:

For Case 2:

For Case 3:

For Case 4:

Download Practice Workbook

This is how you can count based on multiple criteria in different columns using the Excel COUNTIF function. If you want to avoid using the helper column, don’t use COUNITF. Use the other alternative formulas with COUNTIFS, SUMPRODUCT, or SUM I have shown here (depending on what Excel version you are using). I have also covered how to count based on different types of criteria, e.g., AND, AND-OR, greater than, etc. If you know any other methods or face any problems that we have not discussed in this post, please share them with us in the comment box.

Related Articles

• COUNTIF Between Two Values with Multiple Criteria in Excel
• How to Use COUNTIF Between Two Dates and Matching Criteria in Excel
• Excel COUNTIF Function with Multiple Criteria & Date Range
  
• How to Use COUNTIF with Multiple Criteria in the Same Column in Excel
• How to Use COUNTIF for Cells Not Equal to Text or Blank in Excel
• How to Apply SUM and COUNTIF for Multiple Criteria in Excel
• How to Use COUNTIF Function Across Multiple Sheets in Excel