Formula Friday- COUNTIFS With Multiple Criteria In Excel

Today’s formula fun is inspiried by a question I received from Lyn in -to my email box earlier this week. The question was how to count a number of items in a list that met more than one criteria, for example how many orders of Beannie_H hats were for Size Large.

The easiest way to answer these type of questions with the COUNTIFS function. COUNTIFS is available in Excel 2007 onwards.

The syntax of the Formula is

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

So, here is our sample extract of order data.

We want to know how many orders of Beannie_H hats in size LARGE were placed in the above list of orders.

Select the cell that you want to type the formula in
Hit = and the type COUNTIFS(
Next select the cells that contain the values you want to check for the first criterion- in this example it is the Product _Ref Column which is C2:C15
Hit comma then “Beannie_H” (Because this is a text criterion, it is enclosed in double quotation marks)
Type comma to begin the next set of criterion- in this case it is D2:D15
With a final comma we can then Type”LARGE”

Your formula should look like this

We have 5 orders for LARGE Beannie_H hats in our order data.

Instead of typing in criterion, you can refer to a cell instead- in which case the formula would look like this

If you wanted to use operators such as greater than or less than or equal to (<>=), then they need to be enclosed on double quotation marks. So, your formula would look like this