Latest posts by techwriter (see all)
- Should Technical Writing be Boring? And if Yes, Why? - November 15, 2017
- How to Create a Custom-Designed Header in MS Word that Would be Available to All Other Word Documents - November 13, 2017
- What is the Difference Between Expository Writing and Technical Writing? - November 8, 2017
By BJ Johnston
This article is inspired by a question I was asked by a website visitor. The question was along the lines of 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 two criteria being for hats called Beannie_H and Size Large.
The easiest way to answer these type of questions is with the COUNTIFS function in Excel.
COUNTIFS is available in Excel versions 2007 onwards.
The syntax of the Formula is
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]… )
So, here is our sample extract of order data to base our query.
Order Product Size Volumes
342 Beannie_H LARGE 45
343 Beannie_H SMALL 48
344 Beannie_J LARGE 65
345 Beannie_H LARGE 100
346 Beannie_H LARGE 75
347 Beannie_H LARGE 23
348 Beannie_H LARGE 25
349 Beannie_J SMALL 78
350 Beannie_J LARGE 200
351 Beannie_J MEDIUM 100
352 Beannie_J LARGE 43
353 Beannie_J SMALL 57
354 Beannie_J LARGE 45
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 where our criteria of Beannie_H is held in cell C4
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
=COUNTIFS(C2:C15,C3,E2:E15,”>50″) this will search for any orders that are for Beannie_H hats for volumes over 50.
BJ Johnston has been an advanced Excel user for 15 years and is the creator of http://www.howtoexcelatexcel.com a site that shares Excel tips and tricks with it’s enthusiastic members.