Microsoft Office Tutorials and References
In Depth Information
Using “Or” criteria
Using “Or” criteria
Suppose that you want to get a sum of past-due invoice amounts or ones associated with the
Oregon office (regardless of their past-due status). In other words, the value in the Amount
range is summed if either of the following criteria is met:
h The corresponding value in the Difference range is negative.
h The corresponding text in the Office range is “Oregon” .
This type of sum requires an array formula:
=SUM(IF((Office=”Oregon”)+(Difference<0),1,0)*Amount)
A plus sign (+) joins the conditions, and you can include more than two conditions.
When you enter an array formula, remember to press Ctrl+Shift+Enter.
Using “And” and “Or” criteria
Things get a bit tricky when your criteria consists of both And and Or operations. For example,
you might 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 consists of two conditions, joined with Or. The following array
formula does the trick:
=SUM((Difference<0)*IF((Office=”Oregon”)+(Office=”California”),1)*Amount)
 
Search JabSto ::




Custom Search