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

in order to count how many orders for Beannie_H that were for over 100 units, in this case it is 2.

Want to Watch The Video Of This Tip?

 

 

Want to see your Excel tips on YouTube??- subscrible to How To Excel At Excel Channel.

 

Create Multiple Reports From One Pivot Table

Break Your Pivot Table Filters Into Multiple Columns

Default Your Pivot Tables To SUM not COUNT

Excel Tutorial -Creating An Excel Table

Delete obsolete items from your Pivot Tables blog post or watch the YOUTUBE video..

 

 

 

 

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts