COUNTIF with Multiple Criteria

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

COUNTIF with Multiple Criteria in Excel

We can use the COUNTIF function with multiple criteria methods in Excel with the concatenation operator or AND operator in criteria or the OR operator as required.

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

Table of contents

Method #1: Using the SUM with COUNTIF Function.

The following are the steps used to find the sum using the COUNTIF function: -

  1. We must first copy the below data to our Excel sheet.


    Using SUM With COUNTIF function 1

  2. Then, we need to apply the below SUM formula with COUNTIF to get the total count of Paseo and Montana.


    Using SUM With COUNTIF function 1-1

    And the result will be as per the below image.

    Using SUM With COUNTIF function 1-2

    Using SUM With COUNTIF function 1-3

    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.

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

  • Step 1: First, we need to copy the below data to our Excel sheet.
Using Double COUNTIF function 1
  • Step 2: Next, we must apply the below formula to get the total of Paseo & Montana.
Using Double COUNTIF function 1-1

And the result will be as per the below image.

Using Double COUNTIF function 1-2

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:

Using Double COUNTIF function 1-3
  • Part 1: This is the normal COUNTIF formula 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 Function in Excel Explained in Video

 

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.

COUNTIF Function with Multiple Criteria 1

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

COUNTIF Function with Multiple Criteria 1-1

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.

  1. First formula result = 12
  2. Second formula result = 5
  3. Result = A – B
  4. Result = 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.