COUNTIFS with Multiple Criteria (5 Easy Methods)

COUNTIF counts cells in a range depending on a single criterion. Whereas COUNTIFS count cells in one or multiple ranges depending on multiple criteria. This article discusses different uses of COUNTIFS with multiple criteria such as Texts, Values, Dates, and Multiple Columns.

In that scenario suppose, a dataset contains Food Sales data with selling dates, categories, products, quantity, and unit price.

dataset

Dataset for Download

5 Easy Ways to use COUNTIFS with Multiple Criteria

Method 1:Using COUNTIFS with Array Constant

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.

Step 1: Enter the text  =SUM(COUNTIFS($C$7:$C$20,{“Cookies”,”Bars”,”Crackers”})) in any cell, the seller wants to display the count.

counifs with array constant

Step 2: Press Enter. The total number of food types will appear

final result of countifs with array constant

Method 2:Using COUNTIFS with Multiple Criteria 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.

Step 1: Insert the text =COUNTIFS($E$7:$E$20,”>50″, $E$7:$E$20,”<100″) in any cell.

countifs between values

Step 2: Hit Enter.The number of sold products’ quantity between 50-100 displays

final countifs between values

Method 3: Using COUNTIFS with Multiple Criteria 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.

Step 1: Type the text =COUNTIFS($B$7:$B$20,”>=”&DATE(2021,10,10),$B$7:$B$20,”<=”&DATE(2021,10,20))in any desired cell.

countifs between two dates

Step 2: Press Enter. The number of sales between 11 Oct to 15 Oct 2021 appears.

final countifs between two dates

Method 4: Using COUNTIFS 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).

Step 1: Insert the Formula =COUNTIFS($C$8:$C$21,C2,D8:D21,C3,$E$8:$E$21,C4) if the seller wants to display the count.

countifs between multiple columns

Step 2: Press Enter. The count of the sales based on criteria gets displayed.

final countifs between multiple columns

Method 5: Using COUNTIFS 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

stock status dataset

Step 1:Type the Formula =COUNTIFS($D$7:$D$20,”*”,$G$7:$G$20,”<>”&””) in any desired cell.

wildcard character

 

Step 2: Press Enter. The count of products that are out of stock appears.

final wildcard character

Keep it in mind that wildcard characters (*”, “?” etc.) can not be used in 2nd criteria.

Conclusion

COUNTIFS counts cells depending on multiple criteria. Using multiple COUNTIFS in one formula can replace the combination of SUM and COUNTIFS functions. But it makes the formula pretty lengthy that’s why we ignore the method. The methods discussed in the article are situation-driven and easy to relate to. Hope you find them convenient to follow. Comment, if further clarifications are needed or you want to add something.

Maruf Islam

I, Maruf Islam, an engineer, content writer. I completed my BSc from Bangladesh University Of Engineering & Technology, want to pursue a career in content writing & development.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo