Microsoft Office Tutorials and References

In Depth Information

**Summing Things Up**

Figure 8-7:

Being

productive

with

SUMPRODUCT.

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:

=SUMIF(C3:C15,”Great Grocery”,B3:B15)

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.