In this article, you’ll learn how to use the COUNTIF function in Excel with multiple criteria in different scenarios with 4 suitable examples.
The examples included using the COUNTIF function to count multiple values from a single column with multiple OR criteria, the SUM function and the SUMPRODUCT function.
You’ll know about using the COUNTIFS function to count multiple values from multiple columns with multiple AND and OR criteria both.
You’ll also learn to apply the COUNTIF function to count numbers between two values and to count dates between two dates.
Suppose a sales dataset that includes columns for region, product, and salesperson is available. The number of sales transactions that satisfy particular requirements can be counted by using the COUNTIF function with multiple criteria.
If you have an inventory spreadsheet with multiple columns like item name, quantity, and category, you can use the COUNTIF function with multiple criteria to count the number of items that match specific conditions.
Below you’ll see an overview image of using the COUNTIF function with multiple criteria in Excel.
Download Practice Workbook
You can download the Excel file from here for practice.
How to Use COUNTIF with Multiple Criteria in Excel
Case 1: Using COUNTIF Function to Count Multiple Values from a Single Column
1.1 With Multiple OR Criteria
- Write the following formula in cell C20 to get the total number of Apple and Banana present in the Fruit column-
=COUNTIF(B5:B17,"Apple")+COUNTIF(B5:B17,"Banana")
Formula Breakdown
- COUNTIF(B5:B17,”Apple”)– This function will count the total number of Apple present in the range B5:B17.
- COUNTIF(B5:B17,”Banana”)– This function will count the total number of Banana present in the range B5:B17.
- COUNTIF(B5:B17,”Apple”)+COUNTIF(B5:B17,”Banana”)– Finally, this formula will join them with the OR operator (+) and give the total number of Apple and Banana in the given range.
1.2 With SUM Function
- To calculate the total number of Apple and Banana that is present in the Fruit column, enter the formula below in cell C20–
=SUM(COUNTIF(B5:B17,{"Apple","Banana"}))
Formula Breakdown
- COUNTIF(B5:B17,{“Apple”,”Banana”})– The COUNTIF function counts how many cells in the range B5:B17 contain either Apple or Banana and returns the count for each item individually.
- SUM(COUNTIF(B5:B17,{“Apple”,”Banana”}))– Finally, the SUM function is used to add up the values of the given range returned by the COUNTIF function.
1.3 With SUMPRODUCT Function
- To determine how many Apple and Banana are all together in the Fruit column, enter the formula below in cell C20–
=SUMPRODUCT(COUNTIF(B5:B17,{"Apple","Banana"}))"Banana"}))
Formula Breakdown
- COUNTIF(B5:B17,{“Apple”,”Banana”})– The COUNTIF function counts how many cells in the range B5:B17 contain either Apple or Banana and gives the count for each item separately.
- SUMPRODUCT(COUNTIF(B5:B17,{“Apple”,”Banana”}))– The values of the specified range provided by the COUNTIF function are then added using the SUMPRODUCT function.
Case 2: Using COUNTIFS Function to Count Multiple Values from Multiple Columns
2.1 With Multiple AND Criteria
- Write the following formula in cell C20 to get the total number of fruits greater than 6 in quantity and equal to $50 in sales value-
=COUNTIFS(C5:C17,">6", D5:D17,"=50")
2.2 With Multiple OR Criteria
- Write the following formula in cell C20 to get the total number of Apple whose delivery status is Cancelled or Pending–
=COUNTIFS(B5:B17, "Apple", D5:D17,"Cancelled")+COUNTIFS(B5:B17, "Apple", D5:D17,"Pending")
Formula Breakdown
- COUNTIFS(B5:B17, “Apple”, D5:D17,”Cancelled”)– This function will count the total number of Apple those delivery status is Cancelled from the ranges B5:B17 and D5:D17.
- COUNTIFS(B5:B17, “Apple”, D5:D17,”Pending”)– This function will count the total number of Apple those delivery status is Pending from the ranges B5:B17 and D5:D17.
- COUNTIFS(B5:B17, “Apple”, D5:D17,”Cancelled”)+COUNTIFS(B5:B17, “Apple”, D5:D17,”Pending”)– Finally, this formula will join them with the OR operator (+) and give the total number of Apple those delivery status is Cancelled or Pending from the given range.
Read More: How to Apply COUNTIF with Multiple OR Criteria in Excel
Case 3: Applying COUNTIF Function to Count Numbers between Two Values
- Write the following formula in cell C20 to get the total number of values between 5 and 15 present in the Quantity column-
=COUNTIF(C5:C17,">5")-COUNTIF(C5:C17,">=15")
Formula Breakdown
- COUNTIF(C5:C17,”>5″)– This function will count the total number of values present in the range C5:C17 greater than 5.
- COUNTIF(C5:C17,”>=15″)– This function will count the total number of values present in the range C5:C17 greater than or equal to 15.
- COUNTIF(C5:C17,”>5″)-COUNTIF(C5:C17,”>=15″)– Finally, this formula will give the total number of values between 5 and 15 in the range.
Read More: COUNTIF Between Two Values with Multiple Criteria in Excel
Case 4: Inserting COUNTIF Function to Count Dates between Two Dates
- Type the following formula in cell C20 to get the total number of dates between September 20 and October 10 including these dates present in the Delivery Date column-
=COUNTIF(D5:D17, ">=20/9/2023")-COUNTIF(D5:D17, ">10/10/2023")
Formula Breakdown
- COUNTIF(D5:D17, “>=20/9/2023”)– This function counts the number of cells in the range D5:D17 that contain a date equal to or greater than September 20.
- COUNTIF(D5:D17, “>10/10/2023”)– This function counts the number of cells that contain a date greater than October 10.
- COUNTIF(D5:D17, “>=20/9/2023”)-COUNTIF(D5:D17, “>10/10/2023”)– Finally, this formula will give the total number of dates between September 20 and October 10 including these dates in the given range.
Read More: How to Use COUNTIF Between Two Dates and Matching Criteria in Excel
Things to Remember
- The COUNTIF function works well with only a single criterion. You can use it with multiple criteria using the OR operator (+) and the SUM or SUMPRODUCT But it will be convenient if you use the COUNTIFS function for multiple criteria.
- The COUNTIF function’s criteria argument is not case-sensitive.
Frequently Asked Questions
1. How do I combine multiple COUNTIF formulas?
You can combine multiple COUNTIF formulas using the OR operator (+) just like this –
=COUNTIF(range1, criteria1) + COUNTIF(range2, criteria2) + COUNTIF(range3, criteria3)+…2. How to use COUNTIF with 3 criteria?
You can combine multiple COUNTIF formulas using the OR operator (+) as shown before or you can use the COUNTIFS function instead. Assume you have data in three different ranges from A1:A10, B1:B10 and C1:C10. You want to count the number of rows that meet three criteria: Apple from A1:A10, greater than 10 from B1:B10, and less than 5 from C1:C10. In an empty cell, enter the following formula-
=COUNTIFS(A1:A10, "Apple", B1:B10, ">10", C1:C10, "<5")3. How do I use COUNTIF with UNIQUE?
To use the COUNTIF function in combination with the UNIQUE function, you’ll need to use an array formula. Assuming you have a range of values in the range A1:A10 and you want to count the unique occurrences of a specific value, such as Apple. In an empty cell, enter the following formula as an array formula-
=COUNTIF(UNIQUE(A1:A10),"Apple")If you use the Excel version before Excel 365, you’ll need to press Ctrl + Shift + Enter to enter the formula as an array formula.
Conclusion
In conclusion, you can use the COUNTIF function in Excel with multiple criteria in different scenarios. Although not appropriate for all sorts of multiple conditions, you can still utilize the COUNTIF function in some situations combining it with other functions like the SUM and SUMPRODUCT functions and the OR criteria. You can also use the COUNTIFS function for multiple criteria. All these examples are shown in this article. If you want to know more about this topic, visit the links provided in the Knowledge Hub section of this article.
COUNTIF Multiple Criteria: Knowledge Hub
<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
