COUNTIF Function with Multiple Criteria (Table of Contents)
You can have more than one condition in an If Statement. The VBA keywords And and Or allow use of multiple conditions. These words work in a similar way to how you would use them in English. Let’s look at our sample data again. We now want to print all the students that got over between 50 and 80 marks. We use And to add an extra condition.
COUNTIF with Multiple Criteria
COUNTIF function helps us to count the cells or calculate the number of a certain value in a list. Sometimes for the business purpose, we need to use multiple criteria for counting.
Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more
There can be two ways for multiple counting:
- COUNTIF Function with multiple criteria in the same column.
- COUNTIF Function with multiple criteria in multiple columns.
COUNTIF Formula with Multiple Criteria
Below is the COUNTIF Formula :
The COUNTIF Formula has two arguments i.e. range, criteria.
- Range: The range of cells to count.
- Criteria: This defines the condition that tells the function which cells to count. It can be a number, text string, cell reference, or expression.
Here in this article, we will discuss the COUNTIF Function with multiple criteria in the same column.
As we know that COUNTIF function is a built-in function in excel and it falls under FORMULAS tab. Thus, COUNTIF function can be entered in a cell-like below screenshot:
How to Use COUNTIF Function with Multiple Criteria?
It is very simple and easy to use. Let us understand the working of COUNTIF Function by some COUNTIF Formula example.
You can download this COUNTIF with Multiple Criteria Template here – COUNTIF with Multiple Criteria Template
Example #1
We have given some product list:
We need to find out the count of Laptop & Mobile. As these products are in the same column, hence we will use COUNTIF function here.
We have given two conditions, hence the COUNTIF formula will apply as follows:
= COUNTIF(range, criteria1) + COUNTIF(range, criteria2) + …….
Please refer below screenshot:
![Two Two](/uploads/1/2/4/7/124726247/856508014.jpg)
Explanation:
- Here, we will apply the condition one by one. First will take the range and 1st condition under COUNTIF function. Again will take the same range and 2nd condition under COUNTIF function and so on…
- After applying all the given conditions, sum up all the results to find out the final conclusion.
The final result is:
Example #2
We have given some product details here. In this example, we will use a COUNTIF function between two numbers.
Here we will calculate, the count of items which is greater than 12 and less than 30.
The COUNTIF formula will apply as follows:
=COUNTIF($C$19:$C$30,”>12″)+COUNTIF($C$19:$C$30,”<30″)
Please refer below screenshot:
Explanation:
- We pass the condition one by one and in end sum all the results.
Hence the final result is:
Example #3
Let’s take the same product details again.
Here, we will calculate the sum of products who sold out in the month of Jan, Feb & May.
We will apply the COUNTIF formula as follows:
=COUNTIF($D$40:$D$51,”Jan”)+COUNTIF($D$40:$D$51,”Feb”)+COUNTIF($D$40:$D$51,”May”)
Explanation:
- First, we will select the range of cells from D40:D51 and will pass 1st condition “Jan”.
- Second, we will again select the range of cells from D40:D51 and will pass the 2nd condition “Feb”.
- A third will again select the range of cells from D40:D51 and will pass the 3rd condition “May”.
- After passing all the conditions one by one, in the end, add all the results.
The Final Result is:
Things to Remember about COUNTIF Function with Multiple Criteria
- First, analyze the problem and condition mentioned.
- If the conditions are related to one specific column then will apply the COUNTIF function in a single column as we discussed in the above article.
- If conditions are related to multiple columns, then we use COUNTIFS function which we will discuss in the later article.
Recommended Articles
This has been a guide to COUNTIF with Multiple Criteria. Here we discuss the COUNTIF Formula with Multiple Criteria and how to use the COUNTIF Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –
Software Development Course - All in One Bundle
600+ Online Courses
3000+ Hours
Verifiable Certificates
Lifetime Access
Hello Excel Forum,
I'm a VBA newbie, so please forgive any obvious errors.
I'm stuck in a Do While Loop. I have a user form where a First and Last Name can be entered. When the user clicks a button (Number Check), a worksheet is searched for a Row that contains the First Name (in column C), and the Last Name (in column D). When a row is found with the First and Last Name, a message box displays the row location. This works well if the both First and Last name exist in the spreadsheet. However, if both First and Last names do not exist in the spreadsheet, the program will get stuck in a loop, and i am forced to use Crtl+Pause to exit.
Here's what the spread sheet looks like:
And here's the VBA code:
This is the error I get: Run-time Error '91': Object variable or With block variable not set.
The code is getting hung up at this line: Any suggestions you may have is greatly appreciated.
Thanks!
-Ryan