Excel provides a "quick" way to produce some popular calculations using a process called a function. The format of many of the functions is very similar to the SUM Function. If you understand what is happening on the most popular function, you are a long way towards building the necessary understanding for many, many of the Excel functions.

We will build the "sweetness" of the function process over a formula to help you see the efficiency. In the future, you will decide which (formula or function) you will use.

Without a doubt, adding numbers is the most frequently used arithmetic process on all numerical spreadsheets. Let's take a look at how we direct Excel to add.

	A	B	C	D
1
2		12
3		7
4		6
5
6	Sum =

We would like to put the sum of the three numbers in the yellow cell. We do not want to add the numbers and enter the sum (25). We want to make Excel do the arithmetic!

We could click in cell B6 and enter a formula to make Excel do the work:

=12 + 7 + 6

Important: The = is included in the formula to distinguish to Excel that the numbers that follow are to be calculated.
If you leave off the =, Excel will just display the text characters 12 + 7 + 6 and not find the sum.

	A	B	C	D
1
2		12
3		7
4		6
5
6	Sum =	=12+7 +6

When you complete the formula you will press the Enter key and Excel will compute the value in Cell B6.

	A	B	C	D
1
2		12
3		7
4		6
5
6	Sum =	25

Now imagine you have several numbers to add. Suppose you had to find the sum of the following list:

23, 45, 56, 11, 45, 123, 4, 90, 24, 54, 94, 92, 22, 42, 58, 16, 9, and 5

Your formula would look like this:

=23 + 45 + 56 + 11 + 45 + 123 + 4 + 90 + 23 + 54 + 94 + 92 + 22 + 42 + 58 + 16 + 9 + 5

With so many numbers to enter, there is a probability you will make a keypunch error. Can you find the error in my formula? There is one.

How would you know you had made an error? How would you check to see if you had entered the formula correctly? The checking would be extremely tedious if not impossible to certify correct. There has to be a better way. Thank you Excel for the SUM function!

	A	B	C	D
1
2		12
3		7
4		6
5
6

Let's use this same example and see how it works for a short list of numbers. It will work the same for a long list!

First let's locate the cell addresses of the two end-points of the column of numbers. The 12 is in B2 and the 6 is in B4. We will direct Excel to find the sum of all numbers between the two endpoints by the function. First we click on the yellow cell (B6) and enter the function:

=SUM(B2:B4)

The = says this is an expression to be calculated

The SUM says this is the SUM function

B2 is one endpoint of the column and B4 is the other

The : indicates use all the numbers from B2 through B4

In this case, we have directed Excel to find the sum of the numbers in the range of cells from one endpoint to the other. The sum function is the same whether the list of numbers to be added is 2 or 200!

Try to envision where the list of numbers would be located in the spreadsheet for each of these examples.

1) =SUM(B2:E2)

2) = SUM(C3:C6)

When you have mentally produced an answer, scroll down to see the answer.

The "neat" thing about the sum function is that it works independent of the length of the string of numbers. All you have to do is put them in a row or column and apply the sum function. Another "neat" fact about the sum function is approximately 75% to 80% of all spreadsheets using any arithmetic use this function. Learn to use this function and you are almost finished!

---

As previously mentioned, the Sum function is quite popular. The folks who developed Excel thought you might get tired of typing the sum function and produced a shortcut they call AutoSum. We love shortcuts, so let's take a look at this one.

The task is to get Excel to add this column of numbers without using a formula or having to type the sum function.

 

Note there is a button on the Toolbar that will help.

 

The summation sign or the Sigma sign is used by Excel to give you the AutoSum shortcut.

You can view the name of the button by hovering your mouse over the button and the name will appear.

If you actually click the AutoSum button, you will see an outline of the numbers Excel predicts you are trying to find the sum.

It is important that you look at the suggested range of numbers to see if you agree. Sometimes Excel will not guess correctly what numbers you want to include in the range.

If you agree with the outlined range of numbers, simply press the Enter key and Excel will complete building the Sum function and perform the calculation.

We think you need to be able to enter the Sum function from the keyboard as well as enter using the AutoSum button. At some point in your work, you will have to edit the Sum function to correctly identify the range YOU want.

Return