The Sum Function in Excel 2007
You saw a simple way to add up in the
previous section. Enter an equals sign, followed by the cells you
want Excel to add up:
= B4 + B5 + B6 + B7
But this is not a good way to add up in Excel 2007: it
could get very tedious indeed if you had to type out say 50 cell references
by hand. The easy way is to get Excel to do the work for you. That's
where SUM comes in.
The SUM function
The SUM function is used to add things up, and saves you
the bother of typing out lots of cell names and numbers. It looks like
this:
=SUM( )
In between the round brackets, you type what you want
Excel to add up. Look at our spreadsheet again:
We want to add up the numbers under the Monday
heading, and place the answer in cell B9.
So with cell B9 selected again, click into your formula
bar. If you're following along from the
previous lesson, you should have this in cell B9:
If you have an equals sign before B4, delete it and press
the enter key. Now position your cursor at the start of the line, before
the "B" of B4.
Type an equals sign first, then the letter SU of
SUM.
As soon as you start typing, Excel will present you with a drop down
list of available functions. Click once with the left mouse button on
SUM to highlight it:
Now double click on SUM. Excel will add the "M"
for you, and the left bracket. It will also highlight the cells in your
formula:
Now press the Enter key on your keyboard. Excel will add
the right bracket, and work out the SUM for you:
Now click back on cell B9, and look at the Name box (just
above the A column, in our image). It has B9 in it. The formula bar
to the right shows you which formula you have in the active cell (B9).
An easier way to add up number with the SUM function is to use a colon
(:) The colon is a shorthand way of adding up consecutive cells.
Instead of typing out all those cell references like this:
=SUM(B4 + B5 + B6 + B7)
you can just type out the first cell reference, then a colon, then
the last cell reference. Like this:
=Sum(B4: B7)
Excel will then add up the numbers in cells B4 to B7. It knows what
the colon means!
- Click into cell B9, if it's not already active
- Now click on the cell with your right mouse button
- You'll see a menu appear:
- From the menu, select Clear Contents by clicking the item with
your left mouse button
- This will clear the formula from the formula bar
- Now click back inside of the formula bar and type the following:
=Sum(B4:B7)
Your spreadsheet should look like ours:
When you have the formula typed out, hit the Enter key
on your keyboard. Excel will add up the numbers for you, and place the
correct answer in cell B9.
If everything went well, you should have an answer of 17 in cell B9.
Fortunately, we can use AutoFill for the rest of the answers.
- Place your mouse pointer to the bottom right of cell B9
- The pointer will turn into a thin black cross:
- Hold down your left mouse button
- Keep it held down, and drag your mouse to cell H9:
With your mouse pointer over cell H9, let go of the left
button. Excel will AutoFill the rest of the formulas. It uses the same
formula from cell B9 to get the answers, and just alters all the cell
references. Without AutoFill, you'd have to type it all out yourself!
The answers on Row 9 of your spreadsheet should be the same as ours
in the image below:
Notice the formula bar in the image. It shows the formula
in cell H9. This is:
=Sum(H4:H7)
The formula we started with was:
=Sum(B4:B7)
Excel has changed the letters for us, but not the numbers. In other
words, it's adding up the columns.
If you think of the colon as the word TO, it should make sense:
Add up the cells B4 TO B7
Add up the cells H4 TO H7
In the next section, you'll get some more practice with this spreadsheet,
and with the SUM Function. So don't forget to save the work you've done
so far!
HOME
