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

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

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

**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.**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**:

`=COUNTIF(G7:G21,TRUE)`

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

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

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.

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

`=COUNTIF(G7:G21,TRUE)`

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

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

`=COUNTIF(G7:G21,TRUE)`

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

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

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

`=COUNTIF(G7:G21,TRUE)`

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

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

`=COUNTIF(G7:G21,TRUE)`

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

**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)`

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

**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)`

## 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:

`=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)))`

**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))`

**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)))`

**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

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

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)