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

Get FREE Advanced Excel Exercises with Solutions!

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.

Syntax of Excel COUNTIF function

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.

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

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.

note

The SUM and COUNTIF functions are available in any Excel version.

But the COUNTIFS function is only available from Excel 2010 and the SUMPRODUCT function is from Excel 2007.

We have used Excel for Microsoft 365 to prepare this article.


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).

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

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.

The AND function has the following syntax:

=AND(logical1,[logical2], …)

If both the conditions match then the AND function will return TRUE. If any of the criteria does not match, the AND function will return FALSE.

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

Note: Here, we created a Drop Down List for the criteria. You can choose different Brands and Products from the drop-down list.

Follow these steps:

Step 1: Add a helper column in column G.

Adding helper column

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:

=AND(C7=$C$25,D7=$C$26)
Note: We have used absolute reference for criteria cells (C25 and C26). So these cell references will not change when you copy the formula down the column.

Putting AND function in Helper Column to match the criteria

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

Showing FALSE as criteria don't match after putting AND function in Helper Column to match the criteria

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.

Showing TRUE and FALSE after putting AND function in Helper Column to match the criteria

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

=COUNTIF(G7:G21,TRUE)

Putting Excel COUNTIF with Helper Column Based on Multiple Criteria in Different Columns

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

Showing result after putting Excel COUNTIF with Helper Column Based on Multiple Criteria in Different Columns

So, HP Desktop occurs twice in the sales data.

Tips: If you don’t want to use any helper column, then combine the COUNTIF and helper column formulas together.

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.

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

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:

=AND(C7=$C$25,D7=$C$26,E7=$C$27)

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.

Showing TRUE and FALSE after putting AND function in Helper Column to match the criteria

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

=COUNTIF(G7:G21,TRUE)

Putting Excel COUNTIF with Helper Column Based on Multiple Criteria in Different Columns

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.

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

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 syntax of the OR function is:

=OR(logical1,[logical2], …)

The OR function returns TRUE if any of the conditions is true. If not a single criterion is true, it returns FALSE.

So,

OR(TRUE,FALSE) = OR(FALSE,TRUE) = OR(TRUE,TRUE) = TRUE

But, OR(FALSE,FALSE) = FALSE

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:

=OR(AND(C7=$C$25,D7=$C$27),AND(C7=$C$26,D7=$C$27))

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

Showing TRUE and FALSE after putting OR and AND functions in Helper Column to match the criteria

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

=COUNTIF(G7:G21,TRUE)

Putting Excel COUNTIF with Helper Column Based on Multiple Criteria in Different Columns

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.

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

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

=AND(C7=$C$25,D7=$C$26,F7>$C$28)

Showing TRUE and FALSE after putting AND function in Helper Column to match the criteria

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

=COUNTIF(G7:G21,TRUE)

Putting Excel COUNTIF with Helper Column Based on Multiple Criteria in Different Columns

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

Note: To apply other criteria, like <, <=, >= or =, simply change the > operator inside the formula and type your desired one.

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.

The syntax of the IFS function is:

=IFS(logical_test1, value_if_true1, …)

The IFS function can check multiple conditions one by one.

If the first condition is not TRUE, then it checks whether the 2nd condition is TRUE. If the 2nd one is not TRUE, it checks the 3rd condition and it keeps going on this way.

It returns an assigned value for the first TRUE condition. If no condition is TRUE, then it returns FALSE or the assigned value for FALSE.

It does not check the next condition if the previous one is TRUE.

This function actually a replacement of nested IF functions.

Note: The IFS function is only available in Excel 2019 and Microsoft 365 versions.

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

=AND(C7=$C$25,D7=$C$26,IFS($C$27="=",F7=$C$28,$C$27=">=",F7>=$C$28,$C$27=">",F7>$C$28,$C$27="<=",F7<=$C$28,$C$27="<",F7<$C$28))

Showing TRUE and FALSE after putting AND and IFS functions in Helper Column to match the criteria

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

=COUNTIF(G7:G21,TRUE)

Putting Excel COUNTIF with Helper Column Based on Multiple Criteria in Different Columns

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.

Syntax of the COUNTIFS function is:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The COUNTIFS function counts the number of cells in multiple ranges and for multiple criteria. The criteria can be different but the ranges should be of equal size (same number of rows and columns).

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.

=COUNTIFS(C7:C21,C25,D7:D21,C26)

Putting Excel COUNTIFS Based on Multiple Criteria in Different Columns


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.

=COUNTIFS(C7:C21,C25,D7:D21,C26,E7:E21,C27)

Putting Excel COUNTIFS Based on Multiple Criteria in Different Columns


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:

=COUNTIFS(C7:C21,C25,D7:D21,C27)+COUNTIFS(C7:C21,C26,D7:D21,C27)

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.

Putting Excel COUNTIFS with OR operator Based on Multiple Criteria in Different Columns


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:

=COUNTIFS(C7:C21,C25,D7:D21,C26,F7:F21,C27&C28)

Putting Excel COUNTIFS Based on Multiple Criteria in Different Columns


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.

Syntax of the SUMPRODUCT function is:

=SUMPRODUCT(array1, [array2], [array3], …)

The SUMPRODUCT function returns the sum of the products of given ranges or arrays. The arrays should be of equal dimensions (same number of rows and 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:

Putting Excel SUMPRODUCT Based on Multiple Criteria in Different Columns

=SUMPRODUCT((C7:C21=C25)*(D7:D21=C26))

For other cases, use the following formulas:

In Case 2:

=SUMPRODUCT((C7:C21=C23)*(D7:D21=C25)*(E7:E21=C26))

In Case 3:

=SUMPRODUCT(((C7:C21=C23)+(C7:C21=C24))*(D7:D21=C25))

In Case 4:

=SUMPRODUCT((C7:C21=C23)*(D7:D21=C25)*(IFS($C$27="=",F7:F21=$C$28,$C$27=">=",F7:F21>=$C$28,$C$27=">",F7:F21>$C$28,$C$27="<=",F7:F21<=$C$28,$C$27="<",F7:F21<$C$28)))

Overview of putting Excel SUMPRODUCT Based on Multiple Criteria in Different Columns

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:

=SUM((C7:C21=C25)*(D7:D21=C26))

Putting Excel SUM Based on Multiple Criteria in Different Columns

For Case 2:

=SUM((C7:C21=C23)*(D7:D21=C25)*(E7:E21=C26))

For Case 3:

=SUM(((C7:C21=C23)+(C7:C21=C24))*(D7:D21=C25))

For Case 4:

=SUM((C7:C21=C23)*(D7:D21=C25)*(IFS($C$27="=",F7:F21=$C$28,$C$27=">=",F7:F21>=$C$28,$C$27=">",F7:F21>$C$28,$C$27="<=",F7:F21<=$C$28,$C$27="<",F7:F21<$C$28)))

Overview of putting Excel SUM Based on Multiple Criteria in Different Columns


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


<< Go Back to COUNTIF Multiple Criteria | Excel COUNTIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

2 Comments
  1. I need count one criteria in (A1:A10,”TOM”) & Count how many in C1:C10
    COLUMN A feild “NAME”
    COLUMN C Field in No of “BL” ANSWER= 5
    A C
    TOM 2
    ANN 1
    TOM 1
    ABI 2
    ANN 2
    TOM 2

    • Reply Avatar photo
      Musiha Mahfuza Mukta Feb 27, 2023 at 10:58 AM

      Thank you ZOYSA for your comment. Below, I have attached two formulas for your problem.
      I have written the data from the A2 cell and C2 cell. According to your question, I have considered the dataset till A10 and C10.
      Firstly, write the below formula in the E2 cell or the cell from where the NAME will be started.
      =IF(A2=”TOM”,C2,””)

      Then copy this formula up to E10 or your dataset’s end cell.
      Then use another formula in the F4 cell.
      =SUM(E2:E10)

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo