Adding Up with Excel 2007's SUM and SUMIF Functions - dummies

Adding Up with Excel 2007’s SUM and SUMIF Functions

By Greg Harvey

In Excel 2007, you use the very familiar SUM function to total values in your worksheets. Use the SUMIF function when you want to total only the numbers in a range that meet a criteria you specify. These functions are located with the Math & Trig functions on the Ribbon’s Formulas tab and in the Select a Category list in the Insert Function dialog box.


No function in the entire galaxy of Excel functions comes anywhere close to the popularity of the SUM function. So popular is this function, in fact, that Excel has its own AutoSum command button located on the Home tab of the Ribbon (the one with the on it) that you most often use to build your SUM formulas. You should, however, be aware of the workings of the basic SUM function that the AutoSum button enables you to use so easily.

For the record, the syntax of the SUM function is as follows:


When using the SUM function, only the number1 argument is required; this is the range of numbers in a cell range or array constant that you want added together. Be aware that you can enter up to a total of 29 other optional number arguments in a single SUM formula, all of which are separated by a comma (,). For example, you can build a SUM formula that totals numbers in several different ranges, as in this formula:


In this example, Excel sums the values in the cell range B3:B10 on Sheet1, Sheet2, and Sheet3 of the workbook, giving you the grand total of all these values.


What about those times when you only want the total of certain items within a cell range? For those situations, you can use the SUMIF function. The SUMIF function enables you to tell Excel to add together the numbers in a particular range only when those numbers meet the criteria that you specify. The syntax of the SUMIF function is as follows:


In the SUMIF function, the range argument specifies the range of cells that you want Excel to evaluate when doing the summing; the criteria argument specifies the criteria to be used in evaluating whether to include certain values in the range in the summing; and finally, the optional sum_range argument is the range of all the cells to be summed together. If you omit the sum_range argument, Excel sums only the cells specified in the range argument (and, of course, only if they meet the criteria specified in the criteria argument).

The figure illustrates how you can use the SUMIF function to total sales by the items sold. This figure shows a Sales list sorted by the store location (there are three locations: Mission Street, Anderson Rd., and Curtis Way) and then the item sold. To total the sales of lemon tarts at all three locations, you could palce the following SUMIF formula in cell I3:

Using SUMIF to total sales by items sold.
Using SUMIF to total sales by items sold.
=SUMIF(item_sold,"=Lemon tarts",daily_sales)

In this example, item_sold is the range name given to the cell range C3:C62, which contains the list of each item that has been sold in the first five days of January 2008 (Lemon tarts, Blueberry muffins, Lots of chips cookies, or Strawberry pie), and daily_sales is the range name assigned to the cell range G3:G62, which contains the extended sales made at each store for each item.

The SUMIF formula in cell I3 looks for each occurrence of “Lemon tarts” in the item_sold range (the criteria argument for the SUMIF function) in the Item column of the Cookie Sales list and then adds its extended sales price from the daily_sales range in the Daily Sales column to the total.

The formulas in cells I4, I5, and I6 contain SUMIF functions very similar to the one in cell I3, except that they substitute the name of the dessert goodie in question in place of the =Lemon tarts criteria argument. The formula in cell I8, however, is slightly different: This formula sums the sales for all items except for Strawberry pies. It does this with the SUMIF function in the following formula:

=SUMIF(item_sold,"<>Strawberry pie",daily_sales)

Because the item Strawberry pie is preceded with the not (<>) operator (which can be placed before or after the open double quotation mark), Excel sums the daily sale for every item except for Strawberry pie.