How to Add Column data in Microsoft Excel using the SUM Function
Use the SUM function to calculate the total amount of a column.
Thursday, August 3, 2006
A common task when using Microsoft Excel is to add up a group of numbers or values in a column. The way to do this in Excel is to use the SUM function. The formulas below show you how to add up values in columns using a few different variations of the SUM function:
To get the sum of values in a column (e.g. column B), use the following formula:
=SUM(B6:B17)
Here's how to have the sum appear directly beneath the last cell:
- Drag your mouse cursor over the cells you want to add (e.g. B6 thru B17).
- Click on the AutoSum button on the toolbar (AutoSum icon looks like a squiggly letter E).
- The total sum will appear in the first empty cell below the last cell in the formula (e.g. B18).
Here’s how to get the sum of values to appear where you want it:
- Click on the cell where you want the Total sum to appear (e.g. D20).
- Now click on the AutoSum button on the toolbar.
- Now put the mouse cursor on the first cell you want to add up and drag it to the last cell that should be part of the formula. Note: you can use this technique for rows or columns.
- Press Enter or click the AutoSum button again to get the total sum.
Here’s how to use the Function Arguments dialog box to get the sum of values:
- Click on the cell where you want the Total sum to appear (e.g. A21).
- Press the equal sign (=) on your keyboard.
- Click on the function drop-down list box (See Figure 1), on left side of the formula bar. Note: the Name box changes when you type an equal sign in the formula bar, to a drop-down list that displays your last 10 recently used functions.
- Click on SUM. If you don’t see SUM in the list you can select “More Functions…” to find it.
- A Functions Arguments dialog box will pop up. See Figure 2.
- Click on the icon in the Number1 text box. (Note: the Function Arguments box will become smaller).
- Now drag your mouse cursor over the cells that you want to add together (e.g. A6 thru A17). See Figure 3.
- Press Enter.
- Click OK.
To get the total sum of values in two columns (e.g. column B and column F, see Figure 4), use the following formula:
=SUM(B6:B17,F6:F17)
or
=SUM(B6:B17)+SUM(F6:F17)
To add values for an entire range of a column (e.g. column B, see Figure 5), use the following formula:
=SUM(B:B)
The entire range formula is useful when new data is added or deleted from a table on an ongoing basis, and the number of rows in a column continues to vary. Essentially, this formula eliminates the need to continually edit your formula when new data is added.
With the help of this step-by-step tutorial, you should now have a better understanding of the basics on how to calculate the total values in an Excel spreadsheet column using the SUM function.