The COUNTIF function counts cells in a range depending on a single criterion. Whereas the COUNTIFS function counts cells in one or multiple ranges depending on multiple criteria. This article discusses different uses of the COUNTIFS Function with multiple criteria such as Texts, Values, Dates, and Multiple Columns. We will go through 4 easy methods to do the task.
How to Use COUNTIFS Function with Multiple Criteria in Excel: 4 Easy Examples
The following dataset has the Order Date, Category, Product, Quantity, and Unit Price columns. Further using this dataset, we will go through 4 easy examples of using COUNTIFS with multiple criteria. Here, we used Excel 365. You can use any available Excel version.
1. Applying the COUNTIFS Function with Constant Array
In this method, we will use COUNTIFS with a constant array. From the dataset, the seller wants to count the Cookies, Bars, and Crackers types of food sales. In this case, using a combination of SUM and COUNTIFS can do the job.
Steps:
- First of all, we will type the following formula in merged cell B21:C21.
=SUM(COUNTIFS($C$5:$C$18,{"Cookies","Bars","Crackers"}))
- After that, press ENTER.
- Therefore, the total number of food types will appear in the merged cell B21:C21.
2. Using the COUNTIFS Function with Multiple Criteria for Different Values and Dates
In this method, we will use COUNTIFS between two values, and two dates with multiple criteria.
2.1. Employing COUNTIFS Function Between Two Values
Suppose the seller wants to count the number of sold products Quantity that exceeds the amount of 50 but below 100. In this case, COUNTIFS with multiple criteria eases the work.
Steps:
- In the beginning, we will type the following formula in the merged cells B21:E21.
=COUNTIFS($E$5:$E$18,">50", $E$5:$E$18,"<100")
- Then, hit ENTER.
- As a result, the number of sold products quantity between 50-100 is displayed in cells B21:E21.
2.2. Using COUNTIFS Function Between Two Dates
In a scenario, the seller wants to count the sale numbers between 11/10/2021 to 15/10/2021. Operator (&) is used to direct the reference in the formula.
Steps:
- First of all, we will type the following formula in the merged cells B21:C21.
=COUNTIFS($B$5:$B$18,">="&DATE(2021,10,10),$B$5:$B$18,"<="&DATE(2021,10,20))
- After that, hit ENTER.
- Therefore, the number of sales between 11 Oct to 15 Oct 2021 appears in the merged cells B21:C21.
3. Inserting COUNTIFS Function with Multiple Criteria in Multiple Columns
Sometimes, the seller has to count sales based on criteria that are in multiple columns. In the dataset, suppose, the seller wants to count the sales of products (Chocolate Chip) based on category (Cookies) and a specific quantity(>50).
Steps:
- In the beginning, we will type the following formula in the merged cells D21:D22.
=COUNTIFS($C$5:$C$18,C20,D5:D18,C21,$E$5:$E$18,C22)
- After that, press the ENTER button.
- Therefore, the result appears in the merged cells D21:D22.
4. Using the COUNTIFS Function with Wildcard Characters
In a situation when the seller needs to count the products that are out of stock, he can use Wildcard Characters such as “*”, “?” etc. In a scenario, where the seller has a couple of products out of stock like the image below.
Steps:
- First, we will type the following formula in the merged cells B21:D21.
=COUNTIFS($D$5:$D$18,"*",$G$5:$G$18,"<>"&"")
- Afterward, press the ENTER button.
- Hence, the count of products that are out of stock appears in the merged cells B21:D21.
Practice Section
You can download the Excel file and practice the explained methods.
Download Practice Workbook
You can download the Excel file from the following link and practice while reading this article.
Conclusion
Here, we show you 4 easy examples of using the COUNTIFS function with multiple criteria in Excel. Thank you for reading this article. We hope it was helpful. If you have any queries, please let us know in the comment section.
