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.


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.

excel countif multiple criteria different column

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.

Multiple Criteria of OR Type

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.

excel countif multiple criteria different column

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.

excel countif multiple criteria different column

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.

excel countif multiple criteria different column

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.

Multiple Criteria of AND Type

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.

excel countif multiple criteria different column

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.

excel countif multiple criteria different column

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)

COUNTIF of Single Criteria in Single Column in Excel

Secondly, press ENTER and get the output as 2.

excel countif multiple criteria different column

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.

excel countif multiple criteria different column

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.

excel countif multiple criteria different column

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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo