COUNTIF with Multiple Criteria

In a countif function we have a single criterion and when we have multiple criteria to use to count the instances we use Countifs but there is a method to use Countif with multiple criteria too and the method is by using the concatenation operator or & operator in criteria or the or operator as required.

Table of Contents

COUNTIF with Multiple Criteria
How Countif Function works with Multiple Criteria?

Usually, in excel when we are counting something based on certain multiple criteria’s we use COUNTIFS function to do the job for us. Most of our business requirements are counting certain things or related to counting invoices, counting no., of products, counting the total number of customers etc.

Meeting multiple criteria requires COUNTIF function. The words that are ending with the letter “S” means plural (the singular & plural we all learned in our English classes) meeting 2 or more criteria’s.

Before I explain to you how we can do, I will first explain how COUTNTIF works.

COUNTIF Formula in Excel

This function works based on only one criteria (it has only IF not IFS). The syntax of COUNTIF function involves two parameters.

Range: The range of cells you are counting.
Criteria: In the given range, what do you want to count?

To understand the COUNTIF practically I will give one simple example. Assume you have a list of sales table for different countries with different products as shown in the below image.

From the above list, we need to count the total count for the Paseo product. To get the total count we can use COUNTIF function with multiple criteria.

The formula I have used here is =COUNTIF ($C$2:$C$25,”Paseo”). Now let me break it down the formula.
CountIf ($C$2:$C$25, this is the range we are counting. In this example, this range includes product names.
“Paseo” : This is the Criteria we are giving. In the given range, we need only the count of the product Paseo.

In this way, we can count certain things using COUNTIF function with multiple criteria.

COUNTIF Function with Multiple Criteria

Now we know COUNTIF can match one criterion. What if I want to count the total of Paseo and Montana?

Looks like tricky one right. But we can do this with COUNTIF also. Now let us take the above data for this example too.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects) 4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Previously we have counted the total count of Paseo product. Now we need the total of Paseo + Montana. We can do this in two ways. I will explain both of them in this article. Follow below steps one by one.

You can download this COUNTIF with Multiple Criteria Template here – COUNTIF with Multiple Criteria Template

Method #1: Using the SUM with COUNTIF Function.

Step 1: Copy the below data to your excel sheet.

Step 2: Apply the below SUM formula with COUNTIF to get the total count of Paseo & Montana.

And the result will be as per the below image.

Now I will break down the formula. The formula I have used here is

Part 1: As per the formula, our range to count the product is from C2:C25.
Part 2: If we are counting only one criteria we just mention our criteria in double quotes (“Paseo”). Since we are counting multiple criteria’s we need to mention curly brackets before we mention out criteria’s.
Part 3: Now SUM function plays its part in returning the result. The countif formula returns the count for Paseo (8) and the count for Montana (10). Sum will add Paseo (8) + Montana (10) and returns the result as 18.

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

Step 1: Copy the below data to your excel sheet.

Step 2: Apply the below formula to get the total of Paseo & Montana.

And the result will be as per the below image

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

Part 1: This is the normal COUNTIF formula excel to count the total count for Paseo product.
Part 2: This is the normal COUNTIF formula excel to count the total count for Montana product.

Part 1 returns 8 as the result and Part 2 returns 10 as the result. Plus (+) symbol adds these two numbers and returns the result as 18.