# How to Use COUNTIFS Function with Multiple Criteria in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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. Note: Keep it in mind that wildcard characters (“*”, “?” etc.) can not be used in 2nd criterion.

## Practice Section ## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  