Excel COUNTIF for Multiple Criteria with Different Column

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for Excel COUNTIF multiple criteria with different column, then you are in the right place. While using Excel, we often need to use the COUNTIF function for different purposes, but most of the time to count data. In this article, weâ€™ll try to discuss Excel COUNTIF multiple criteria with different column.

2 Ways to Use COUNTIF for Multiple Criteria with Different Column in Excel

Excel offers 2 ways to use COUNTIF function for multiple column with different criteria.

1. Multiple Criteria of OR Type

We can use COUNTIF for multiple criteria of OR type.

1.1. Using Two COUNTIF Functions

We can use multiple criteria which is mainly OR type with the help of the COUNTIF function.
Let us try to find out how many items have prices greater than \$100 or quantities produced greater than 1000 in the G5 cell.

We can use two COUNTIF functions together to satisfy the two conditions of our problem..
Firstly, write the formula in the G5 cell.

`=COUNTIF(D5:D15,">100")+COUNTIF(C5:C15,">1000")`

Here, D5:D15 refers to the Price Per Piece and C5:C15 refers to the Quantity Produced.

Secondly, press ENTER to get the output as 13.
So, here we have 13 items with prices greater than \$100 or quantities produced greater than 1000.

Note: If we have multiple criteria of the same column, the process is the same.
For example, to find out the number of items with prices less than \$100 or greater than \$200, the formula will be in the G5 cell.

`=COUNTIF(D5:D15,"<100")+COUNTIF(D5:D15,">200")`

1.2. Using SUMPRODUCT Function

We can also use the SUMPRODUCT function to use multiple criteria.

1.2.1. Multiple Criteria of Different Columns

Now, If we have multiple criteria of OR type, and of different columns, we can use two SUMPRODUCT functions to find that.
For example, to find out the number of items with prices greater than \$100 or quantities greater than 1000, firstly write the formula in the G7 cell like this.

`=SUMPRODUCT(--((D5:D15)>100))+SUMPRODUCT(--((C5:C15)>1000))`

Secondly, press ENTER.
Here, we have 13 items with prices greater than \$100 or quantities produced greater than 1000.

1.2.2. Multiple Criteria of the Same Column

If we have multiple criteria of the same column, for example, to find out the number of items with a price less than \$100 or greater than \$200, we can either use the combination of SUMPRODUCT and COUNTIF functions.

Firstly, write the formula in the G9 cell like this.

`=SUMPRODUCT(COUNTIF(D5:D15,{"<100",">200"}))`

Secondly, press ENTER.
Eventually, weâ€™ll get the output as 5.

See, we have 5 items with prices less than \$100 or greater than 200.

2. Multiple Criteria of AND Type

Now let us try another different thing. Let us try to find out how many items are there with prices greater than \$100 and quantities greater than 1000. We can find it out using these two ways.

2.1. Using COUNTIFS Function

Suppose, we need to find out prices greater than \$100 and quantities greater than 1000 in the G6 cell.

Firstly, write the formula in the G6 cell like this.

`=COUNTIFS(D5:D15,">100",C5:C15,">1000")`

Here, D5:D15 refers to Price Per Piece and C5:C15 refers to Quantity Produced.

Secondly, press ENTER to get the output as 3.

See, we have 3 items with prices greater than \$100 and quantity produced greater than 1000.
Note: If you have multiple criteria of AND type, but of the same column, the process is the same.
For example, to find out the number of items with price greater than \$100 and less than \$200, the formula will be in the G6 cell like this.

`=COUNTIFS(D5:D15,">100",D5:D15,"<200")`

2.2. Using SUMPRODUCT Function

This time we will again find out the number of items with a price greater than 100 and quantity greater than 1000, but with SUMPRODUCT() function.
Firstly, write the formula in the G8 cell like this.

`=SUMPRODUCT(((D5:D15)>100)*((C5:C15)>1000))`

Secondly, press ENTER and get the output as 3.

COUNTIF of Single Criteria in Single Column in Excel

We can also use the COUNTIF function to maintain a single criterion in a single column.
Suppose, in the following dataset we want to find out Items with Price Equal to \$200 in the G6 cell.
Firstly, write the formula in the G6 cell like this.

`=COUNTIF(D6:D15,200)`

Secondly, press ENTER and get the output as 2.

Now, if we want to find out Items with Price Greater than \$100, similarly, write the formula in the G7 cell like this.

`=COUNTIF(D6:D15,">100")`

Secondly, press ENTER and get the output as 5.

Now, if we want to calculate Total Number of Items we need to write the formula in the G8 cell.

`=COUNTIF(B5:B15,"*")`

After pressing ENTER, weâ€™ll get the output as 11.

Conclusion

Thatâ€™s all about todayâ€™s session. And these are the ways to convert USD to Euro in Excel. We strongly believe this article would be highly beneficial for you. Donâ€™t forget to share your thoughts and queries in the comments section and explore our website ExcelDemy, a one-stop Excel solution provider.