COUNTIF with Multiple Criteria in Excel

For example, suppose we have a dataset of a list of bottles. We have the number of bottles ( values in column B from B2 to B6)  available in stock and need to be sold ( values in column C from C2 to C6). Therefore, we need to calculate the count of items more than 10 and less than 40. We can achieve this task by using the below formula:

=COUNTIF( $C$2:$C$6, “>10”) + COUNTIF($C$2:$C$6, “<40”)

Method #1: Using the SUM with COUNTIF Function.

Method #2: Using Double COUNTIF Function with Multiple Criteria.

  • Step 1: First, we need to copy the below data to our Excel sheet.

  • Step 2: Next, we must apply the below formula to get the total of Paseo & Montana.

And the result will be as per the below image.

  • We must first copy the below data to our Excel sheet. Then, we need to apply the below SUM formula with COUNTIF to get the total count of Paseo and Montana. And the result will be as per the below image.Now we will break down the formula. The formula we have used here is:Part 1: As per the formula, our range to count the product is from C2:C25.Part 2: If we count only one criterion, we mention our criteria in double quotes (“Paseo”). Since we calculate multiple criteria, we need to mention curly brackets before saying our criteria.Part 3: Now, the SUM function plays its role in returning the result. The COUNTIF formula returns the count for Paseo (8) and Montana (10). The sum will add Paseo (8) + Montana (10) and returns the result as 18.

And the result will be as per the below image.Now we will break down the formula. The formula we have used here is:Part 1: As per the formula, our range to count the product is from C2:C25.Part 2: If we count only one criterion, we mention our criteria in double quotes (“Paseo”). Since we calculate multiple criteria, we need to mention curly brackets before saying our criteria.Part 3: Now, the SUM function plays its role in returning the result. The COUNTIF formula returns the count for Paseo (8) and Montana (10). The sum will add Paseo (8) + Montana (10) and returns the result as 18.

Here we have used two COUNTIF functions with multiple criteria to get the total count of two products. And the formula we have used here is:

  • Part 1: This is the normal COUNTIF formulaCOUNTIF FormulaThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump”
  • read more excel to count the total count for the Paseo product.Part 2: This is the normal COUNTIF formula Excel to count the total count for the Montana product.

Part 1 returns eight as a result, and Part 2 returns ten as a result. Plus (+) symbol adds these two numbers and returns the result as 18.

COUNTIF with Multiple Criteria – Another Example

Now, we will see the total count between the two numbers. We will consider the numbers below and find the total numbers count between 1,000 and 2,000.

Again, we need to apply two COUNTIF formulas to get the total.

In this formula, the first formula helps us find values greater than 1,000, and the second formula enables us to find values greater than 2,000. Once we get the result, we deduct the first formula value with the second formula value.

  • First formula result = 12Second formula result = 5Result = A – BResult = 7

Therefore, the total number count of numbers between 1,000 and 2,000 is 7. In this way, we can use the COUNTIF function with multiple criteria.

This article has been a step-by-step guide to COUNTIF with multiple criteria. Here, we discuss how COUNTIF works with multiple criteria (SUM and Double COUNTIF) and examples and downloadable Excel templates. You may also look at these useful Excel tools: –

  • SumIf with Multiple CriteriaSumIf With Multiple CriteriaThe SUMIF (SUM+IF) with multiple criteria sums the cell values based on the conditions provided. The criteria are based on dates, numbers, and text. The SUMIF function works with a single criterion, while the SUMIFS function works with multiple criteria in excel.read moreSUM Formula in ExcelSUM Formula In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read moreCOUNTIF ExamplesHow to use SMALL Function in Excel?How To Use SMALL Function In Excel?The SMALL Function in Excel returns the nth smallest value from a set of values. For example, 2nd smallest value, 3rd smallest value and so on.read moreRemove Leading Spaces in ExcelRemove Leading Spaces In ExcelThe TRIM function in Excel can remove all the unwanted spaces in the excel cell. read more