How Many Different Ways Can You Sum In Excel 2007?

We tend to take summing for granted in Excel. After all there's the Autosum button to help us, or we could type in the formula from scratch. That's all there is to it isn't it? There are a number of other ways to sum in Excel which may be of interest. This article describes various methods to sum in Excel and perhaps some may be new to you.

First way: Simple formula

Lets suppose there are three numbers in separate cells D4, D5 and D6. The cell locator is in cell D7. We could add the numbers using some basic arithmetic, so you type in (without the quotes) "=D4+D5+D6" and press the Enter key. You can also achieve this by typing "=" and left click on cell D4, then type "+" and left click on cell D5, then type another "+", then left click on cell D6, and press the Enter key to finish. This way we are pointing to the cells with the mouse rather than typing in the cell references as we build the formula.

Of course that's ok for just a few numbers but what if the list contains 20 numbers? This is where we can use one of Excel's in built routines, the SUM function. We'll keep to the three cell example but it could be lots more in reality.

Second way: Inserting SUM function

Delete anything in cell D7 and ensure D7 is still selected. We'll use Excel's Insert Function feature to create our sum. Select the Formula Tab and then on the far left click on the Insert Function button. In the Insert Function panel you will see SUM listed under the "Most Recently Used" category. Click on SUM then OK. A "Functions Arguments" panel appears. If necessary carefully drag the panel by its upper blue bar so that it is not blocking the cells with the numbers. Then carefully select cells D4 to D6 with your mouse, then release the mouse. The highlighted cells shimmer and you'll see "D4:D6" in the "Number 1" box in the panel. Click OK to finish. That's it. Excel adds the formula "=SUM(D4:D6)" into cell D7 and displays the answer.

Third way: Typing the function yourself

If you know the function name you can type it directly into the cell. So try starting again with cell D7 empty, and D7 selected. Now type "=S" and notice that a yellow pop down appears listing all the functions beginning with "S". Then type a "U" and then an "M". You can see that the pop down changes automatically to list all the functions beginning with "SU" and then "SUM". When you see the function you want in the pop down you can double click it and Excel produces "=SUM(" in the cell. Alternatively you can complete typing the letters yourself followed by an open bracket.

Once the open bracket is there, drag the three cells in question, and Excel obligingly shimmers to indicate you selection. Then type a close bracket and press Enter key to complete. You can actually omit the close bracket and Excel will add it for you. Don't forget the open bracket after "SUM", as that locks the action into the current cell.

Fourth way: Select empty cell or cells, then Autosum

Next we'll look at using Autosum. Again ensure that cell D7 is empty and that the cell is selected, so that the locator is immediately below our three numbers. Now click the Autosum button. This is in the Home tab to the far right - the one with the Greek sigma symbol. Excel creates the formula for you in cell D7 "=SUM(D4:D6)" and again obligingly shimmers to show the numbers found. Excel automatically detects numbers immediately above the current cell and suggests these in the formula. Press Enter to finish.

The cell references in the brackets indicate the range of cells you want to sum. If the numbers above the current cell contain a gap then Excel will omit cells above the gap, so be careful. If the shimmering range of cells is wrong then do not press Enter yet, instead carefully re-drag the correct cells, then press Enter to finish.

If there are no numbers in the cells immediately above the current cell, Excel automatically checks to the left. So if you select an empty cell to the right of several cells containing numbers in the same row, and if there's no numbers immediately above the selected cell, Excel will detect the cells to the left and suggest the correct range in the sum formula.

If you have several columns of numbers you can use Autosum to sum all the columns at once. For example suppose you have four columns of numbers arranged in five rows, starting in cells D4 and finishing in cell G8. To sum all the columns at once, select all the empty cells immediately below the table, in this example this would be cells D9 to G9. Then click the Autosum button, and hey presto all the columns are added. Alternatively you could select all cells in the empty column to the immediate right of the table, in this example cells H4 to H8, click the Autosum button, and Excel will sum all the rows across the way.

Fifth way: Select cells containing the numbers first, then Autosum

Alternatively we can highlight the cells containing the numbers first, and click Autosum. Note we are selecting the cells with the numbers, not the empty cells underneath or to the right. Excel will add the sums below the column or columns of numbers. Lets look at this in our three cell example.

Highlight the cells containing the three numbers D4 to D6. Then click the Autosum button. That's it - Excel adds the completed sum formula below the numbers. We can do the same for several columns of numbers. Try highlighting a block of cells containing numbers, and click the Autosum button. Excel sums each column in one go.

Hopefully this article will have given you some ideas on summing in different ways. You might consider attending an Excel training course to develop your skills further, and no doubt find other interesting ways to do your sums.