Microsoft Office Tutorials and References

In Depth Information

**Conditional Sums Using Multiple Criteria**

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 example, you may want to sum the values in the
Amount
range when both of the

following conditions are met:

h
The corresponding value in the
Difference
range is negative.

h
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))}