COUNTIF with Multiple Criteria in Excel
Countif with multiple criteria method in excel can be used with the concatenation operator or & operator in criteria or the or operator as required.
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.
COUNTIF with Multiple Criteria – Another Example
Now we will see the total count between two numbers. Consider the below numbers and find the total numbers count in between 1000 and 2000.
Again, we need to apply two COUNTIF formulas to get the total.
In this formula, the first formula helps us to find the values greater than 1000 and in the second formula helps us to find the values greater than 2000. Once we get the result, we deduct the first formula value with second formula value.
- First Formula Result = 12
- Second Formula Result = 5
- Result = A – B
- Result = 7
Therefore, the total number count of numbers between 1000 and 2000 is 7. In this way, we can use the COUNTIF function with multiple criteria.
Recommended Articles
This has been a step by step guide to COUNTIF with multiple criteria. Here we discuss how COUNTIF works with multiple criteria (using SUM and Double Countif) along with examples and downloadable excel templates. You may also look at these useful excel tools –
- SumIf with Multiple Criteria
- IF Formula in Excel
- SUM Formula in Excel | Examples
- OFFSET in VBA
- COUNTIF Examples
- How to use OFFSET Function?
- How to use LARGE Function?
- How to use MODE Function?
- How to use SMALL Function in Excel?
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
