Page 315 - Excel 2007 Bible

19_044039 ch14.qxp 11/21/06 11:06 AM Page 272
Part II
Working with Formulas and Functions
If you’re using Excel 2007, the following formula does the job:
=SUMIFS(Amount,Difference,”<0”,Office,”Oregon”)
The array formula that follows returns the same result and will work in all versions of Excel.
{=SUM((Difference<0)*(Office=”Oregon”)*Amount)}
Using Or criteria
Suppose that 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:
n The corresponding value in the Difference range is negative.
n The corresponding text in the Office range is “Oregon”.
This example requires an array formula:
{=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 may 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:
n The corresponding value in the Difference range is negative.
n The corresponding text in the Office range is “Oregon” or “California”.
Notice that the second condition actually consists of two conditions joined with Or. The following array for-
mula does the trick:
{=SUM((Difference<0)*IF((Office=”Oregon”)+
(Office=”California”),1)*Amount)}

272