Working with multiple criteria can be troublesome work. In this article, we are going to show you how to count rows with multiple criteria in Excel.
We have a dataset of a computer and mobile retailer showing product sales on a daily basis. It has 4 columns: Date, Product, Brand, and Qty Sold (Quantity). Our aim is to count rows that fulfill our given criteria.
How to Count Rows with Multiple Criteria in Excel: 6 Ways
1. Using COUNTIF Function to Count Rows with Multiple Text Criteria in Excel
We want to find out the number of rows that have products sold more than 3 but less than 7. The COUNTIF function can be used to count rows with multiple criteria. For this, we can try to find out more than 3 and more than 7, and finally, take the difference to reach our goal.
Steps:
- Select the empty cell in our criteria. Here, we selected cell G13.
- Then type the following formula:
=COUNTIF(E5:E16,G12)-COUNTIF(E5:E16,H12)We are using the COUNTIF function twice. The first time, it is counting the values of Qty Sold which is more than 3 based on the criteria greater than 3. The second time, it is counting values based on criteria more than 7 of Qty Sold column. Finally, it is taking the difference to find out our desired value.
- Press ENTER.
We will get the value 6. The values are highlighted in our rows to reflect our results.
Here, we are using cell reference in our formula. However, it can be done it directly as well. To do it directly type the following formula and then press ENTER.
=COUNTIF(E5:E16,">"&3)-COUNTIF(E5:E16,">"&7)We have counted rows that satisfy our criteria.
Read More: How to Count Rows with Text in Excel
2. Applying COUNTIF Function with Multiple Date as Criteria to Count Rows in Excel
Now, we’re going to find out how many rows fall inside our predefined date range. Our criteria are to find out the row number containing the dates more than 15 March but less than 18 March.
Steps:
- Type the following formula in cell G13 (We’ve merged cell G13 and H13):
=COUNTIF(B5:B16,">"&G12)-COUNTIF(B5:B16,">"&H12)Again there are two COUNTIF functions in our formula. The first one, counting the dates more than 15 March and the second one, counting the dates more than 17 March. We are taking the difference between those two to find out our value.
- Press ENTER.
Our count is 10 rows that fulfill our multiple criteria.
3. Using SUMPRODUCT Function with AND Criteria to Count Rows in Excel
In this method, we’re going to use the SUMPRODUCT function to count rows. We want to count the number of instances of Lenovo branded products sold more than 3 times.
Steps:
- Type the following formula in cell G13:
=SUMPRODUCT((E5:E16>G12)*(D5:D16=H12))First, we’re finding the Quantity(Qty) Sold more than 3. Then we’re limiting our findings to only the brand Lenovo. We use the multiple sign (*) for AND operation.
- Press ENTER.
We’ve found 2 which represents the count of 2 rows maintaining the given criteria. If we check our dataset, there are two rows that have both “Lenovo” and “more than 3”.
Read More: How to Count Filtered Rows with Criteria in Excel
4. Count Rows with Multiple Criteria in Excel Using SUMPRODUCT Function with OR Criteria
We can use the SUMPRODUCT function to find values with OR criteria too. We aim to count the number of rows that have brand names: Lenovo OR Apple.
Steps:
- Type the following formula in cell G13:
=SUMPRODUCT((D5:D16=G12)+(D5:D16=H12))We use the plus (+) sign for OR operation. In our formula, we’re saying we want to count the rows with the text either Apple or Lenovo.
- Press ENTER.
The operation will show 7. We can see there are 7 instances of the two brands in our dataset.
Read More: How to Count Visible Rows in Excel
5. Utilizing COUNTIFS Function Based on Multiple Criteria to Count Rows
The COUNTIFS function is very useful when working with multiple criteria. We count rows with multiple criteria easily by using this function. We’re going to find out how many days Apple products were sold more than 3 times.
Steps:
- Type the formula from below in cell G13.
=COUNTIFS(E5:E16,G12,D5:D16,H12)In our formula, we’re setting the first criteria as Quantity(Qty) Sold more than 3 in cell G12, and Brand Apple in cell H12 as the second criteria.
- Press ENTER.
We can see the result is 4. We’ve marked our dataset to verify our formula. There were 4 times when Apple products were sold in our dataset.
6. Employing COUNTIFS Function with Multiple Date as Criteria to Count Rows
This time, our goal is to count the number of rows that fall between the dates as more than 15 March but less than 18 March.
Steps:
- Type the following formula in cell G13:
=COUNTIFS(B5:B16,">"&G12,B5:B16,"<="&H12)The First part (B5:B16,”>”&G12) is to find the dates more than 15 March. And the next part (B5:B16,”<=”&H12) is finding the dates less or equal to 17 March.
- Press ENTER.
We will get our desired value. We count 10 rows that match our criteria.
Read More: How to Use Excel to Count Rows with Value
Practice Section
We have included practice datasets for every method to count rows with multiple criteria in the Excel file. You can Practice using those.
Download Practice Workbook
Conclusion
We’ve discussed 6 methods of how you can count rows with multiple criteria in Excel. If you face any problems understanding any of the methods, feel free to reach out to us via the comment section. Thanks for reading, keep excelling!
