MULTI IN ONE: The Sum Function

<div dir="ltr" style="text-align: left;" trbidi="on">
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>= B4 + B5 + B6 + B7</b></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
But this is not a good way to add up in Excel: 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.</div>
<h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The Excel SUM function</h3>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=SUM( )</b></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In between the round brackets, you type what you want Excel to add up. Look at our spreadsheet again. Here it is in Excel 2007:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Excel 2007 Spreadsheet" border="1" height="200" src="http://www.homeandlearn.co.uk/excel2007/images/chocAddiction4.gif" width="591" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In Excel 2010 and 2013 you'll have this less colourful version:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Spreadsheet in Excel 2010 and 2013" height="222" src="http://www.homeandlearn.co.uk/excel2007/images/2013/section2/spreadsheet_1.gif" width="601" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
We want to add up the numbers under the <b>Monday</b> heading, and place the answer in cell B9.</div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Adding up in Excel 2007" border="1" height="245" src="http://www.homeandlearn.co.uk/excel2007/images/chocAddictionSum.gif" width="366" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Type an equals sign first, then the letter <b>SU</b> of SUM.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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 <b>SUM</b> to highlight it:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Function List in Excel 2007" border="1" height="198" src="http://www.homeandlearn.co.uk/excel2007/images/FunctionsSum.gif" width="341" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The start of the SUM function" border="1" height="247" src="http://www.homeandlearn.co.uk/excel2007/images/FunctionsSum2.gif" width="436" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Now press the Enter key on your keyboard. Excel will add the right bracket, and work out the SUM for you:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Excel 2007 has completed the addition" border="1" height="243" src="http://www.homeandlearn.co.uk/excel2007/images/FunctionsSum3.gif" width="436" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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).</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
An easier way to add up number with the SUM function is to use a colon <b>(:)</b> The colon is a shorthand way of adding up consecutive cells. Instead of typing out all those cell references like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=SUM(B4 + B5 + B6 + B7)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You can just type out the first cell reference, then a colon, then the last cell reference. Like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=Sum(B4<span style="color: red;">:</span> B7)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Excel will then add up the numbers in cells B4 to B7. It knows what the colon means!</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Click into cell B9, if it's not already active</li>
<li>Now click on the cell with your right mouse button</li>
<li>You'll see a menu appear:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Clear Contents" height="320" src="http://www.homeandlearn.co.uk/excel2007/images/FunctionClear.gif" width="243" /></div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>From the menu, select <b>Clear Contents</b> by clicking the item with your left mouse button</li>
<li>This will clear the formula from the formula bar</li>
<li>Now click back inside of the formula bar and type the following:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=Sum(B4:B7)</b></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Your spreadsheet should look like ours:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Using the SUM function with the colon" border="1" height="246" src="http://www.homeandlearn.co.uk/excel2007/images/FunctionsSum4.gif" width="366" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Place your mouse pointer to the bottom right of cell B9</li>
<li>The pointer will turn into a thin black cross:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="AutoFill from B9 to H9" border="1" height="219" src="http://www.homeandlearn.co.uk/excel2007/images/AutoFillSum.gif" width="626" /></div>
<ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<li>Hold down your left mouse button</li>
<li>Keep it held down, and drag your mouse to cell H9:</li>
</ul>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Drag the cursor to H9" border="1" height="219" src="http://www.homeandlearn.co.uk/excel2007/images/AutoFillSum2.gif" width="631" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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!</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The answers on Row 9 of your spreadsheet should be the same as ours in the image below:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Day Totals Complete" border="1" height="244" src="http://www.homeandlearn.co.uk/excel2007/images/AutoFillSum3.gif" width="636" /></div>
<div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Notice the formula bar in the image. It shows the formula in cell H9. This is:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=Sum(H4:H7)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The formula we started with was:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=Sum(B4:B7)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Excel has changed the letters for us, but not the numbers. In other words, it's adding up the columns.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
If you think of the colon as the word TO, it should make sense:</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<span class="TextIndent3" style="margin-left: 5em;">Add up the cells B4 <b>TO</b> B7</span><br /><span class="TextIndent3" style="margin-left: 5em;">Add up the cells H4 <b>TO</b> H7</span></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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!</div>
</div>

The Sum Function

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 ...