Microsoft Office Tutorials and References

In Depth Information

=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:

• The corresponding value in the
Difference
range is negative.

• The corresponding text in the
Office
range is
Oregon.

The following array formula does the job:

{=SUM(IF((Office=”Oregon”)+(Difference<0),1,0)*Amount)}

A plus sign (+) joins the conditions; you can include more than two conditions.

Using And and Or criteria

As you might expect, things get a bit tricky when your criteria consists of both And and Or operations. For ex-

ample, you may want to sum the values in the
Amount
range when both of the following conditions are met:

• The corresponding value in the
Difference
range is negative.

• The corresponding text in the
Office
range is
Oregon
or
California.

Notice that the second condition actually consists of two conditions, joined with Or. Using multiple SUMIFS

can accomplish this:

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

+SUMIFS(Amount,Difference,”<0”,Office,”California”)

The following array formula also does the trick:

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