COUNTIFS and SUMIFS

Yesterday, my friend Alan posed a problem. He wanted to count the number of rows that met criteria in two separate columns. He was frustrated that COUNTIF wouldn't allow him to do that.

Since Excel 2007, there have been a couple of functions that have allowed you to check against multiple criteria being satisfied: COUNTIFS and SUMIFS.

COUNTIFS

COUNTIFS is exactly the same as COUNTIF, except that instead of checking against one range, it checks against many. All of the conditions must be true for the entry to be counted.

For example, in a list of employees you may want to count all people in the New York office earning over $50,000. Or in a risks and issues register, you may want to count all risks with a high likelihood and high impact.

The syntax is as follows:

=COUNTIFS(Range1,Criterion1,Range2,Criterion2,…)

So you basically list the ranges contain the things that you want to check, each followed by the condition that you want to check for. And as long as the areas match up, you're golden.

=COUNTIFS(A:A,"New York",B:B,">=50000")

This will count all rows in which column A contains the value "New York" and column B contains a value in excess of 50,000. Note that any check other than an exact match must be encased in double quotes.

SUMIFS

This behaves in much the same way. And the syntax is only subtly different.

=SUMIFS(SumRange,Range1,Criterion1,Range2,Criterion2,…)

You merely plonk your sum range in front of your list of ranges and criteria.

=SUMIFS(C:C,A:A,"New York",B:B,">=50000")

If stock options are housed in column C, then the above formula will total the stock options for all New York employees earning over $50,000.

• Tweet