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

Custom Search