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.

Search JabSto ::

Custom Search