Print this chapterPrint this chapter

Statistical Functions in Microsoft Excel

The Sum Function

Use the SUM function to calculate totals for a range of cells or a group of selected cells on a worksheet. We will use the SUM function for the Budget Detail worksheet to calculate the totals in row 12. Note that several methods for adding a function to a worksheet will be demonstrated throughout this chapter. The following illustrates how to add a function to a worksheet by typing it into a cell location:

  1. Click the Budget Detail worksheet tab to open the worksheet

  2. Click cell C12

  3. Type an equal sign: =

  4. Type the function name SUM

  5. Type an open parenthesis: (

  6. Click cell C3 and drag down to cell C11. This places the range C3:C11 into the function

  7. Type a closing parenthesis: )

  8. Press the ENTER key; the function calculates the total for the Monthly Spend column, which is $1,496

Figure 1 shows the appearance of the SUM function added to the Budget Detail worksheet before pressing the ENTER key.

Budget Detail Worksheet showing range of cells included in function to calculate total of values when "Enter" is pressed.

Figure 1 Adding the SUM Function to the Budget Detail Worksheet


As shown in Figure 1, the SUM function was added to cell C12. However, you also need this function to calculate the totals in the Annual Spend and LY Spend columns. You can copy and paste the function into these cell locations because of relative referencing. Relative referencing serves the same purpose for functions as it does for formulas. The following demonstrates how the total row is completed:

  1. Click cell C12 in the Budget Detail worksheet

  2. Click the Copy button in the Home tab of the Ribbon

  3. Highlight cells D12 and E12

  4. Click the Paste button in the Home tab of the Ribbon. This pastes the SUM function into cells D12 and E12 and calculates the totals for these columns

  5. Click cell F11

  6. Click the Copy button in the Home tab of the Ribbon

  7. Click cell F12, then click the Paste button in the Home tab of the Ribbon; since we now have totals in row 12, we can paste the percent change formula into this row


Figure 2 shows the output of the SUM function added to cells C12, D12, and E12. In addition, the percent change formula was copied and pasted into cell F12. Notice that this version of the budget is planning a 1.7% decrease in spending compared to last year.

Totals were added to cell range C12:E12 and percent change formula was pasted in cell F12 indicating spending will decrease

Figure 2 Results of the SUM Function in the Budget Detail Worksheet

Integrity Check

Cell Ranges in Statistical Functions

When you intend to use a statistical function on a range of cells in a worksheet, make sure there are two cell locations separated by a colon and not a comma. If you enter two cell locations separated by a comma, the function will produce an output but it will be applied to only two cell locations instead of a range of cells. For example, the SUM function shown in Figure 3 will add only the values in cells C3 and C11, not the range C3:C11.

A comma indicates functions will only be applied to cells C3 and C11, and not the range.

Figure 3 SUM Function Adding Two Cell Locations