Quick, clean, and to the point

How to use the COUNTIFS function

In this video, we'll look at how to use the COUNTIFS function to count cells that meet a multiple criteria.

Let's take a look.

In this first set of tables, we have two named ranges, "number" and "color". In column G, I'll enter a formula that satisfies the conditions in column E.

The COUNTIFS function takes accepts arguments in pairs, the first item in the pair is the range, and the second is the criteria. Note that ranges that you use must always be the same size.

For the first example, I need to enter "number" for range and 15 for criteria, just like COUNTIF function.

For the second example, we see the two pairs in action. The "number" range appears twice, each time with a different condition. You can see that there's one number greater than 10 and less than 25.

In the next example, we bring in the second range, "color" with it's own criteria and the formula returns 2.

Finally, we count numbers greater than 0 and less than 25 with a color of "red". The result is 1.

In the next set of tables, we are using a named range called "fruit"

The first example shows that COUNTIFs like COUNTIF is not case-sensitive.

In the next example we see that COUNTIFS conditions are joined with AND. We get a result zero because there are no cells that contain both "apple" and "pear"

If you want to count cells with either "apple" or "pear" you'll need to use a different syntax. One option is to simply to use two COUNTIF functions together. Just add one to the other.

Another option is to use a list to specify two items in one condition. You'll need curly braces and also wrap the entire COUNTIF function in a SUM function.

Both formulas return 4.

In the last set of tables, we have a named range called "date" and a named range called "department".

For the first example, we use the named range date, and ">1/1/2013" for the criteria. Just as if we were using COUNTIF.

In the second example, we use the "date" range twice with two criteria and get 2.

In the last example we use "date" for the first range with a criteria of <1/1/2012. Then we use the range "department" with "Sales" for the criteria.

The formula returns 1.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.