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))}
Search JabSto ::

Custom Search