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.

COUNTIF Multiple Criteria

Download Practice Workbook

COUNTIF Multiple Criteria.xlsx

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")

Inserting COUNTIF function with OR operator to get total number of 2 values

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.

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"}))

Inserting COUNTIF function with SUM function to get total number of 2 values

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.

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"}))

Inserting COUNTIF function with SUMPRODUCT function to get total number of 2 values

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.

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")

Inserting COUNTIFS function with AND operator

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")

Inserting COUNTIFS function with OR operator

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.

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")

Inserting formula to count numbers between two values

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.

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")

Inserting formula to count dates between two dates

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.