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..