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)