Al Ikram Amit
The COUNTIF function in Excel counts the number of cells within a range that meet a given condition. You cannot apply multiple conditions with the COUNTIF function directly. However, there are ways to use this Excel function for more than one criterion using some tricks. In this article, I will show 3 examples of using the COUNTIF function in Excel with multiple criteria.
Download Practice Workbook
You can download the practice workbook from the following download button.
Can We Use Excel COUNTIF Function with Multiple Criteria?
The COUNTIF function of MS Excel is not designed to match multiple criteria. Look at the following image taken from Excel UI. It can take only one range and with a single criterion.
The COUNTIF function was first introduced in the 2007 version of Excel. However, they very soon realized that another function is needed to match multiple criteria easily. Consequently, in the Excel 2010 version, MS Excel introduced a new function which is known as COUNTIFS.
However, if you are still a 2007 version user, no worries. There are some ways with which you can work with multiple conditions using the COUNTIF function. However, we don’t recommend continuing to use it, rather you should use updated Excel versions since the older versions don’t have some awesome newer Excel functions and features.
3 Examples of Using COUNTIF Function in Excel with Multiple Criteria
The COUNTIF function in Excel may enable us to compute the number of instances of a specific value in a list. However, there are occasions when we need to employ numerous criteria for counting, which makes things more complicated. Today, I will discuss some examples of using the COUNTIF function with multiple criteria. In this data set, we have some computer components in the column of Product, Product ID, Date, and Name.
Example 1: Use COUNTIF Function to Count Cells Between Numbers
Excel users are occasionally required to count the number of cells where the value is between two specified values. In the example below, I want to know the outcome of a number between 2000 and 5000.
📌 Steps:
- In the cell E16, insert the following formula:
=COUNTIF($C$5:$C$14,">2000")-COUNTIF($C$5:$C$14,">5000")Here,
- COUNTIF($C$5:$C$14,”>2000″) will count cells greater than 2000.
- COUNTIF($C$5:$C$14,”>5000″) will count cells less than 5000.
- So, the above formula will find cells for 2000 < cells < 5000.
- Now, press Enter.
Read More: COUNTIF with Multiple Criteria in Different Columns in Excel (Both Single and Multiple Criteria)
Example 2: Apply COUNTIF with Multiple Criteria for Dates
The COUNTIF functions can let you count the cells depending on a date range. For example, I want to count the cell numbers in a column that has the date between 5/1/2022 and 8/1/2022.
📌 Steps:
- In the cell E16, insert the following formula:
=COUNTIF($D$5:$D$14,">5/1/2022")-COUNTIF($D$5:$D$14,">8/1/2022")
Here,
- COUNTIF($D$5:$D$14, “>5/1/2022”) will count cells greater than 5/1/2022.
- COUNTIF($D$5:$D$14, “>8/1/2022”) will count cells less than 8/1/2022.
- So, the above formula will find cells for 5/1/2022 < cells < 8/1/2022.
- Now, hit the Enter button.
Read More: Excel COUNTIF Function with Multiple Criteria & Date Range
Example 3: Use COUNTIF with Multiple Criteria for Text
For instance, I have the below data that includes various products in it, and I’d like to figure out how many CPU [Processor] and RAM [Memory] are populated in a single column.
📌 Steps:
- Apply the following formula in the cell E16:
=COUNTIF($B$5:$B$14,"CPU [Processor]")+COUNTIF($B$5:$B$14,"RAM [Memory]")
Here,
- COUNTIF($D$5:$D$14, “>5/1/2022”) will count cells with text CPU [Processor].
- COUNTIF($D$5:$D$14, “>8/1/2022”) will count cells with text RAM [Memory].
- So, the above formula will find cells with text CPU [Processor] & RAM [Memory].
- Now, press Enter.
Read More: How to Apply COUNTIF Not Equal to Text or Blank in Excel
How to Count Multiple Matches with COUNTIF in Excel Utilizing Wildcard Characters
If you have just one criterion and want to get all possible matches, you can easily do this using wildcard characters in Excel with the COUNTIF function.
Excel has three wildcard characters:
- Asterisk (*)
- question mark (?)
- Tilde (~)
Example:
As an example, we will count all the names starting with the letter E.
Asterisk (*): It may represent an unlimited number of characters. In the example below, E* might stand for Emma, Evens, and Eric.
📌 Steps:
- Write the following formula in the cell E16:
=COUNTIF(E5:E14,"E*")
- Then, press Enter.
Excel COUNTIFS: A Better Alternative to COUNTIF Function for Multiple Criteria
If you are a user of Excel 2010 or more updated versions, you can perform the same tasks with the COUNTIFS function in Excel. Remember the first example. You could count the number of products having product IDs in the 2000 to 5000 range with the following formula easily but with COUNTIFS we can set criteria as your requirement. The following formula has 3 criteria for COUNTIFS however in COUNTIF, we can use only 1 criterion.
=COUNTIFS($B$5:$B$14,"CPU [Processor]",$C$5:$C$14,">3000",E5:E14,"John")
Read More: COUNTIF Multiple Ranges Same Criteria in Excel
Conclusion
Follow these steps and stages to apply the COUNTIF function in excel with multiple criteria. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section of our blog ExcelDemy.
