Microsoft Office Tutorials and References

In Depth Information

The SUMIFS function (introduced in Excel 2007) can be used to sum a range when multiple conditions are met.

The first argument of SUMIFS is the range to be summed. The remaining arguments are 1 to 127 range/cri-

terion pairs that determine which values in the sum range are included. In the following examples, alternatives

to SUMIFS are presented for those workbooks that are required to work in versions prior to 2007.

Using And criteria

Suppose you want to get a sum of both the invoice amounts that are past due as well as associated with the Ore-

gon office. In other words, the value in the
Amount
range will be summed only if both of the following criteria

are met:

• The corresponding value in the
Difference
range is negative.

• The corresponding text in the
Office
range is
Oregon.

The SUMIFS function was designed for just this task:

=SUMIFS(Amount,Difference,”<0”,Office,”Oregon”)

For use with versions prior to Excel 2007, the following array formula also does the job:

{=SUM((Difference<0)*(Office=”Oregon”)*Amount)}

This formula creates two new arrays (in memory):

• A Boolean array that consists of TRUE if the corresponding
Difference
value is less than zero; FALSE oth-

erwise

• A Boolean array that consists of TRUE if the corresponding
Office
value equals
Oregon;
FALSE otherwise

Multiplying Boolean values result in the following:

• TRUE * TRUE = 1

• TRUE * FALSE = 0

• FALSE * FALSE = 0

Therefore, the corresponding
Amount
value returns nonzero only if the corresponding values in the memory ar-

rays are both TRUE. The result produces a sum of the
Amount
values that meet the specified criteria.

You may think that you can rewrite the previous array function as follows, using the

SUMPRODUCT function to perform the multiplication and addition:

=SUMPRODUCT((Difference<0),(Office=”Oregon”),Amount)

For some reason, the SUMPRODUCT function does not handle Boolean values properly, so the formula

does not work. The following formula, which multiplies the Boolean values by 1, does work: