Microsoft Office Tutorials and References
In Depth Information
Summing Things Up
Using SUMIF and SUMIFS
SUMIF is one of the real gemstones of Excel functions. It calculates the sum of
a range of values, including only those values that meet a specified criterion.
The criterion can be based on the same column that is being summed, or it
can be based on an adjacent column.
Suppose you use a worksheet to keep track of all your food-store purchases.
For each shopping trip, you put the date in Column A, the amount in Column
B, and the name of the store in column C. You can use the SUMIF function to
tell Excel to add all the values in Column B only where Column C contains
“Great Grocery”. That’s it. SUMIF gives you the answer. Neat!
Figure 8 -8 shows this example. The date of purchase, place of purchase, and
amount spent are listed in three columns. SUMIF calculates the sum of
purchases at Great Grocery. Here is how the function is written for the example:
A couple of important points about the SUMIF function:
✓ The second argument can accommodate several variations of
expressions, such as including greater than (>) or less than (<) signs or other
operators. For example, if a column has regions such as North, South,
East, and West, the criteria could be <>North, which would return the
sum of rows that are not for the North region.
✓ Unpredictable results occur if the ranges in the first and third arguments
do not match in size.