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

=COUNTIFS(C2:C15,”Beannie_H”,D2:D15,”LARGE”)

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

=COUNTIFS(C2:C15,C4,E2:E15,”>50″)

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.