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
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.