Using the AutoSum icon
Because calculating totals is a popular function to perform with spreadsheets Excel has created an easy to
use AutoSum icon for the process. The icon (shown in Figure 2) is in the Standard toolbar, and it is the
only icon with the Greek letter Σ AutoSum works on consecutive cells of data, or a range, entered into a
single column or row.
Figure 2. The AutoSum icon is shown in the red square.
To use the icon to calculate the sum of numbers entered into a range
1. Click on the empty cell that follows the last cell of data in that column
2. Click on the AutoSum icon
3. Press Enter
For example, to calculate the total number of cats that Dr. Smith treated over the entire year, click on the
cell B12, and then follow steps 2 and 3. (Step 2 is illustrated in Figure 3.) Excel generates the sum of 2558,
and enters it into B12.
Figure 3. The AutoSum icon automatically calculates the sum of the nearest range of cells.
To use the icon to calculate the sum of numbers entered into a row of cells, first click on the empty cell
adjacent to the last cell of data in that row, and then follow steps 2 and 3 from the instructions above.
Note: This technique is only useful when the data that needs to be summed is entered into consecutive cells.
If there is a gap or blank in the data, the AutoSum icon will only calculate the total of the range of cells
after the gap or blank.
Entering in a Summation Function Manually
Excel gives you the option of manually entering in functions that apply user defined processing to data in the
spreadsheet. To manually enter a summation function that calculates the total of data entered in adjacent cells
in a column:
1. Click on the cell where you want the sum to appear.
2. Type =Sum(aN1:aN2) where a is the column or row in which the range appears, N1 is the first cell in the
range, and N2 is the last cell in the range.
3. Press Enter.
For example, to calculate the total number of animals that Dr. Smith saw in the month of January:
1. Click in cell G2.
2. Type =Sum(B2:E2) as shown in Figure 4.
3. Press Enter.
Excel calculates the sum as 389 and enters it into cell G2.
Figure 4. When manually entering the sum function, Excel draws a temporary box around the range of cells
specified.
This technique, unlike using the AutoSum icon, allows you to calculate the sum of data in multiple ranges. The
Sum function can work on a set of ranges, and the ranges are specified in the same format but are commaseparated.
For instance, if you wanted to calculate the total number of snakes treated during the entire year, you have two
options:
1. You could enter the summation function as =Sum(E2:E13). Excel will properly calculate the total by treating
July's snake count as a zero.
2. You could enter the summation function as =Sum(E2:E7, E9:E13) (shown in Figure 5) which calculates the sum of
two ranges of cells and entirely omits July.s snake count.
Figure 5. To calculate the sum of 2 or more ranges of cells, use a comma to separate the cell ranges that are passed
to the Sum function.
Using Visual Basic to calculate sums
If you know Visual Basic, the programming language, you can create custom functions, or User Defined functions, in
Excel. These custom functions can be used in the same way as builtin functions such as the Sum function are used,
and they can have functionality that is specific and unique to your needs. To create a custom function:
1. In Excel, press Alt+F11.
2. Select Module from the Insert menu.
3. Type Visual Basic code into the module screen.
4. Press Alt+Q to save the function.
For example, to specify a function called VeterinarySum that calculates the sum of a consecutive range of cells,
follow the steps above and enter the following Visual Basic code for step 3:
Function VeterinarySum(Rcells As Range)
Dim i As Integer
Dim Rcell As Range
For Each Rcell In Rcells
i = i + Rcell
Next Rcell
VeterinarySum = i
End Function
To use a User Defined Visual Basic function:
1. In Excel, click in the cell where you want to place the results of your function.
2. Click the fx icon.
3. In the Insert Function popup window, select User Defined in the dropdown box next to the text Or select a category.
4. Select your user defined function from the menu below the dropdown box.
5. Click OK.
6. Specify the parameters for the function, if there are any.
7. Click OK.
For example, to use the VeterinarySum function to calculate the total number of dogs treated over the entire year:
1. Click in cell C14.
2. Follow steps 2 and 3 from above. Step 3 is shown below in Figure 6.
3. Select VeterinarySum from the drop down box.
4. Click OK.
5. Manually enter C2:C13 in the Rcells text box. This step is shown in Figure 7.
6. Click OK.
Excel calculates the sum of 1819 and enters it into cell C14.
