Microsoft Office Tutorials and References

In Depth Information

**Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS()**

See

function.TolearnmoreabouttheSUMPRODUCT function,see

Using

Conditional

Formulas

with

Multiple

Conditions:

SUMIFS()

SUMIFS()
,,

AVERAGEIFS()

AVERAGEIFS()
, and

COUNTIFS()

When someone sees how easy using SUMIF() is, they invariably want the

function to do more. One of the most frequent questions at the MrExcel mes-

sage board is along the lines of this:
“
I am using SUMIF() to get a total by

region. How can I put two conditions in there to only get the total for a certain

region and product?
”
In legacy versions of Excel, there were ways to do this,

but they were difficult. You had to use either SUMPRODUCT(), DSUM(), or an

array formula. There is a lot of complexity in going from a simple SUMIF() to

the intricate Boolean logic required to understand SUMPRODUCT().

, and
COUNTIFS()

Tip

The order of the arguments differs between SUMIF and SUMIFS. The

sum_rangeis the first argument in SUMIFS but the third argument in

SUMIF. It seems pretty common that you would be editing a SUMIF

function to add additional conditions. Remember to move the sum_range

to be the first argument when you are moving from SUMIF to SUMIFS.

Thankfully, Excel 2007 added plural versions of SUMIF(), COUNTIF(), and

AVERAGEIF() that can handle not just two conditions, but up to 127 condi-

tions. The three new functions add the letter S to the end of the function name

(that is, SUMIFS(), COUNTIFS(), and AVERAGEIFS()) to signify that multiple

IFs are being considered. With SUMIFS() and AVERAGEIFS(), you first specify

the range to be summed or averaged. You then specify pairs of arguments. In

each pair, you first specify the range to check and then the value to match

in that range. The following sections describe these three functions.

Syntax

SUMIFS(sum_range,criteria_range1,criteria1[,criteria_range2, criteri-

a2...])

The SUMIFS() function adds the cells in a range that meet multiple criteria.