Microsoft Office Tutorials and References

In Depth Information

**Using Or criteria**

If the worksheet won’t be used by anyone running a version prior to Excel 2007, the

following formula does the job:

=SUMIFS(Amount,Difference,"<0",Office,"Oregon")

The following array formula 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 ofﬁ ce. 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,
or

■
The corresponding text in the Ofﬁ ce 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:

■
The corresponding value in the Difference range is negative.

■
The corresponding text in the Ofﬁ ce range is
Oregon
or
California
.

Notice that the second condition actually consists of two conditions joined with Or. The

following array formula does the trick:

{=SUM((Difference<0)*IF((Office="Oregon")+

(Office="California"),1)*Amount)}

Summary

This chapter provided valuable tips and tricks that will assist in creating formulas that

handle particular counting and summing operations in a worksheet. Applying any of the

examples covered in this chapter will save you signiﬁ cant time while making your

worksheets into more powerful business tools. At this point, you should understand how to: