Using SUM and AutoSUM

Using the SUM function

SUM is the most commonly performed function in Excel - because we add things up more than we do just about anything else!

You can invoke the SUM function in two different ways, either typing the function name – SUM - directly into the cell or using the AUTOSUM button on the Home tab.

Either way, you can rely on Excel to guess which cells you want to sum, something it’s designed to do – and it’s usually correct – or you can select the individual cells or range of cell yourself. You can also easily edit the range to be summed if Excel guesses wrong or you simply change your mind.

So here I've clicked at the foot of a column of numbers, and when I click the Autosum button on the Home tab, Excel inserts the function, including the equal sign, and then adds parentheses to hold the range of cells to add up. Excel also guesses which cells I want to sum, and it guessed correctly – I’m in cell C12, so Excel sees numbers above that in column C, so it guesses I want to total them.

I press Enter, and the function is applied, and the result appears in the cell.

Let me repeat that for the next 3 columns. See? Quick and simple, and thanks to an orderly worksheet structure in terms of where I put all my data, it’s easy for Excel to guess which cells I want to AutoSUM.

It also works for rows. Here, I’ll sum the sales for the first city, by clicking in cell G6. When I click AutoSUM, Excel guesses I want to sum Row 6, and that’s the range it inserts into the function. I press Enter and the result appears.

And if I use the Fill handle, the tiny black box in the lower right corner of the cell? I can ask Excel to SUM the rest of the cities by dragging through the cells at the right end of all the cities quarterly sales.

But what if Excel guesses wrong?

If you’re in the process of performing the AutoSUM, just click and/or drag through the cells you wanted to SUM. Here, Excel guessed wrong because it had no idea, based on the active cell, which cells I’d like to SUM. So I’ll drag through the cells I wanted, which are for Philadelphia and New York.

Let me do that again to tally the Midwestern Sales and then for the West divisions.

If I want to edit an AutoSUM (or any formula) that’s already completed, just select the cell containing the function, and use the Formula bar to edit it, or you can double-click the function, right in the cell. Either way, you can then select the range that's currently being summed. Notice the cells involved are also shaded within the worksheet.

So, now with the current range selected, I can click and/or drag through the cells I want, and that replaces the original range.

Here, the blank row between my Grand Total cell and the numbers I want to SUM caused Excel to guess wrong, so I simply redirected it.

You can also type the SUM function, if you don't want to use the Autosum button. Just type the equal sign and then the word SUM, and press the TAB key - which inserts a pair of parentheses to contain the range of cells you want to sum. You’ll also notice a series of functions appears in a list underneath the cell you’re typing in – Excel offers that list in case you don’t know the exact name of the function you want, and you can double-click the function you want from that list, too – it works the same as pressing TAB.

At that point, I can drag through any range of cells, or if I use the CTRL key, I can gather single cells and also drag through any range or ranges. Once I have the cells I need selected, I press Enter to execute the function.

So, to get a quick SUM for the European division, I can use the SUM function in cell G18 and then drag through all the quarters’ sales for all 4 cities. When I press Enter, I’ve got my grand total, without having to SUM all the cities and quarters individually.

Now, last, want to sum a series of columns or rows really fast?

Just select a series of cells and click Autosum, and Excel totals the columns above those cells.

This also works in rows, as I can sum the numbers running across the worksheet here.

So, between the SUM function and its automatic use with AutoSUM, you can add things up quickly, easily, and accurately, with just a few clicks!