Microsoft Office Tutorials and References

In Depth Information

**APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE**

SUMIF(range,criteria,sum_range) [Category: Math]

Adds the cells speciﬁ ed by a given criteria. Guru Tip: Added in Excel 97 to make

simple conditional sums easier than using array formulas or SUMPRODUCT.

To look through the items in B2:B20 for rows that match "ABC", then add up the

corresponding revenue from column C, use =SUMIF($B$2:$B$20,"ABC",$C$

2:$C$20). While most people will use a sum_range that is the same size and

shape as the range, you can specify just the top left cell of the sum_range and

Excel will expand the range to be the same size and shape as the range: =SUM

IF($B$2:$B$20,"ABC",$C$2). In the unusual case where range and sum_range

are the same, you can omit the third argument. This happens when you are

testing if the numbers to be added are larger than a threshold as in cell F18

below. Also see page 12

SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2,…]

[Category: Math]

Adds the cells in a range speciﬁ ed by multiple criteria. Guru Tip: New in Excel

2002. While SUMIF can only test for a single condition, SUMIFS can test for up

to 127 pairs of criteria. Note that the argument order is rearranged from SUMIF.

In the plural version of SUMIFS, the sum_range appears ﬁ rst, followed by a pair

of arguments for each criteria. Using the same data set as shown in SUMIF, the

following image shows how to ﬁ nd sales by sales rep and item using SUMIFS.