The image below is an overview of using the COUNTIF function with multiple criteria in Excel.
Download Practice Workbook
Method 1 – Using COUNTIF Function to Count Multiple Values from a Single Column
1.1 With Multiple OR Criteria
- Enter the following formula in cell C20 to get the total number of Apple and Banana present in the Fruit
=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”}))– 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 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.
Method 2 – Using COUNTIFS Function to Count Multiple Values from Multiple Columns
2.1 With Multiple AND Criteria
- Enter 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
- Enter 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”)– 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.
Method 3 – Applying COUNTIF Function to Count Numbers between Two Values
- Enter 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″)– 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
Method 4 – Inserting COUNTIF Function to Count Dates between Two Dates
- Enter the following formula in cell C20 to get the total number of dates between September 20 and October 10 including the 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”)– 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.
COUNTIF Multiple Criteria: Knowledge Hub
<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
