Rifat Hassan
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.
Download Practice Workbook
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")Read More: COUNTIF Between Two Values with Multiple Criteria in Excel
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.
Read More: How to Apply SUM and COUNTIF for Multiple Criteria in Excel
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.
Read More: COUNTIF with Multiple Criteria in Different Columns in Excel
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.
Read More: Excel COUNTIF Function with Multiple Criteria & Date Range
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.
Read More: How to Apply COUNTIF Not Equal to Text or Blank in Excel
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.
Further Readings
- How to Use COUNTIF Function Across Multiple Sheets in Excel
- INDEX, MATCH, and COUNTIF Functions with Multiple Criteria
- How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
- Apply COUNTIF Function in Multiple Ranges for Same Criteria
- How to Use COUNTIF Between Two Dates and Matching Criteria in Excel
