# COUNTIF Multiple Criteria (All Things You Need to Know)

The image below is an overview of using the COUNTIF function with multiple criteria in Excel.

### 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.

### 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.

## 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!
Sajid Ahmed

Sajid Ahmed, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, assumes the position of an Excel & VBA Content Developer at ExcelDemy. A self-motivated individual, his profound interest in research and innovation aligns seamlessly with his passion for Excel. In this role, Sajid not only adeptly addresses challenges but also demonstrates enthusiasm and expertise in gracefully navigating complex situations. This underscores his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF