Al Ikram Amit
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.
Download Practice Workbook
You can download the practice workbook from the following download button.
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:
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.
4 Examples of Applying COUNTIF with Multiple OR Type Criteria in Excel
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.
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.
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.
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.
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 add 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.
Read More: How to Apply SUM and COUNTIF for Multiple Criteria in Excel
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
- How to Use COUNTIF Function Across Multiple Sheets in Excel
- Excel COUNTIF Function with Multiple Criteria & Date Range
- Apply COUNTIF Function in Multiple Ranges for Same Criteria
- How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
- INDEX, MATCH, and COUNTIF Functions with Multiple Criteria
