Microsoft Office Tutorials and References

In Depth Information

**Conditional Sums Using Multiple Criteria**

In SUMIFS, the first argument is the range to be summed. The remaining arguments define the

criteria and come in pairs. Each pair consists of the criteria range followed by the criteria.

For use with earlier versions of Excel, the following array formula also does the job:

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

This formula creates two new arrays (in memory):

h
A Boolean array that consists of TRUE if the corresponding
Difference
value is less than

zero; FALSE otherwise

h
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 arrays 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:

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

Using Or criteria

Suppose you want to get a sum of past-due invoice amounts,
or
ones associated with the Oregon

office. In other words, the value in the
Amount
range will be summed if either of the following

criteria is met:

h
The corresponding value in the
Difference
range is negative.

h
The corresponding text in the
Office
range is
Oregon.