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




Custom Search