Microsoft Office Tutorials and References

In Depth Information

**Using Other Functions**

SUMIF and SUMIFS

Although technically listed as a Math & Trig

function, SUMIF acts like a Logical function so

it’s listed here. Use SUMIF to add a group of cells

only if some condition is true. The syntax for the

SUMIF function is as follows:

The last argument in the SUMIF function is

optional, which means that if you want to

compare values in a range and add those same values

if some condition is true, you can leave this last

argument out. For example (using the worksheet

shown in Figure 3-15), suppose you want to add

all the values in the range E5:E17 that are over

$3 million. You could type =SUMIF(E5:E17,

“>3000000”).

=SUMIF(RangeToCheck,”Condition”,RangeToSum)

For example, consider the worksheet shown

in Figure 3-15. In order to compute the total

of all sales in the Eastern region, this formula is

entered in cell I6: =SUMIF(C5:C17,“Eastern”,E5:E17).

Basically, this formula looks at each cell in the

range C5:C17, and for the ones that contain

the word Eastern, it adds the value in the

corresponding cell in column E.

Tip

The Condition argument in the SUMIF

and SUMIFS functions must be entered

in quotations, as in “Eastern” and “>10”.

The SUMIFS function is similar to SUMIF, except

that it allows you to enter multiple conditions to

identify the cells you want to add. The syntax for

the SUMIFS function is as follows:

=SUMIFS(RangeToSum,RangeToCheck,“Condition”,

RangeToCheck2,“Condition2”,...and so on)

Looking at the worksheet in Figure 3-15,

suppose you want to add up all the sales over $1

million for the Eastern region only. In cell I12,

you could type =SUMIFS(E5:E17,C5:C17,“Eastern”,

E5:E17,“>1000000”).

Figure 3-15

Use SUMIF to add selected cells.