How to Apply COUNTIF with Multiple OR Criteria in Excel

Get FREE Advanced Excel Exercises with Solutions!

Most of the time Excel Users may opt to utilize the COUNTIF function with multiple criteria. So, if you are looking for the use of a COUNTIF function with multiple criteria and then you have come to the right place. In this article, we will demonstrate how to use the COUNTIF function with multiple criteria in Excel.


Can You Do a COUNTIF with Multiple Criteria in Excel?

To answer this question, let’s have a deep-down overview of the function first.

  • COUNTIF Syntax:
=COUNTIF(range, criteria)

Counts the number of cells within a range that meet the given conditions.

  • Arguments:

range– Cells along rows or columns or both.

criteria– The requirements under which the function will count cells.

So COUNTIF alone cannot do multiple criteria operations in Excel. You have to do some tricks for that. In this article, we will see some of its examples.


Applying COUNTIF with Multiple OR Type Criteria in Excel: 4 Useful Examples

First of all, we will utilize the state-wise export data of the USA and their corresponding largest market in the year 2018. We will deal with the columns of States, Exports in USD, % of GDP, Largest Market, and Largest Export Product.

The state-wise export data of the USA on the topic of excel COUNTIF OR multiple criteria


1. Using COUNTIF Function with Multiple OR Type Criteria in a Single Column

Here, we will use multiple criteria in the COUNTIF function within the same column in Excel. Suppose I want to find out how many times the country named Canada, or the Largest Export Product called transportation equipment has appeared in the table. The necessary steps are given below.

📌 Steps:

  • Suppose we want to count the number of iterations of various items like the number of Canada appearing in the Largest Market, or the repetition of export goods like transportation equipment.
  • Now we have set the criteria and the total count will appear in the cell of E16.
  • To do so, we will enter the following formula:
=COUNTIF(E5:E14,"Canada")+COUNTIF(F5:F14,"transportation equipment")

Here,

E5:E14= The column where a particular is placed.

“Canada”= That particular item that will be counted.

Similarly,

F5:F14= The column where a particular is placed.

“transportation equipment”= That particular item that will be counted.

Now the number will be added by the whole formula.

Using COUNTIF Function with Multiple Criteria in a Single Column (OR Logic)

Read More: COUNTIF Between Two Values with Multiple Criteria in Excel


2. Applying COUNTIF Function as an Array for Multiple Criteria Located at Separate Cells

Here, we will use the COUNTIF function as an array for multiple criteria in different columns in Excel. Suppose we want to count the total number of % of GDP greater than 10, the number of Canada, and the repetition of export goods like transportation equipment. The steps are given below.

📌 Steps:

  • Now in this second method, we will count the number of iterations of various items like the number GDP greater than 10 in the column of ‘% of GDP’; the number of Canada appears in the Largest Market or the repetition of export goods like transportation equipment.
  • At this moment, we have set the criteria and the total count will appear in the cell of H8:H10.
  • To do so, most importantly select multiple cells at once.
  • And then we will enter the following formula:
=COUNTIF(C5:E14,G8:G10)

Here,

C5:E14 = % of GDP, Largest Market, and Largest Export Product are the column of C, D, and E.

G8:G10 = greater than 10, the number of Canada, and the repetition of export goods like transportation equipment are the three criteria that will be searched in the column of C, D, and E.

Applying COUNTIF Function as ARRAY

Read More: COUNTIF with Multiple Criteria in Different Columns in Excel


3. Applying COUNTIF to Count Non-Blank or Blank Cells Using Wildcards

Basically, there are 3 slots in our dataset. All of them have lists of % of GDP and Largest Export Product. We have to count only % of GDP from Slot A, the Largest Export Product from Slot B & all-blank cells from Slot C. Then we’ll make a sum of all counts under these categories.

📌 Steps:

  • Firstly, in Cell G11, type-
=COUNTIF(B5:B19,"*")+COUNTIF(C5:C19,">0")+ COUNTIF(D5:D19,"<>"&"")

Here,

Here we have used the COUNTIF functions for all 3 slots separately. Then we simply added all these counts by inserting Plus (‘+’) in the function bar.

Applying COUNTIF to Count Non-Blank or Blank Cells

Read More: How to Apply COUNTIF Not Equal to Text or Blank in Excel


4. Using the SUMPRODUCT with COUNTIF Function

In this last method, we will try the COUNTIF function with the SUMPRODUCT function. The products of the COUNTIF function are going to be added by the SUMPRODUCT function.

📌 Steps:

  • First of all, in Cell G11, enter the following formula:
=SUMPRODUCT(COUNTIF(D5:D14,{"Canada";"Mexico"}))

Here,

  • The COUNTIF functions here have counted Canada, Mexico in the cells range in the D5:D14. Then the number will be added by the function of SUMPRODUCT.
  • And the total count is 8.

Using the SUMPRODUCT with COUNTIF Function

Read More: How to Apply SUM and COUNTIF for Multiple Criteria in Excel


Download Practice Workbook

You can download the practice workbook from the following download button.


Conclusion

Follow these steps and stages in Excel to COUNTIF with multiple OR criteria. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section of our blog ExcelDemy.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Hello, I'm Amit, a BUET graduate with a passion for Excel. Currently, I work as an Excel & VBA Content Developer at ExcelDemy, contributing insightful articles to the blog. I enjoy sharing my knowledge with others and always look for opportunities to grow as an Excel expert. Feel free to reach out if you need assistance or want to discuss the latest trends in Excel. Let's excel together in the world of data manipulation and analysis!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo