Simple functions, Sum
• Excel provides a large number of special
functions that can be used in formulas.
• For example, a commonly used function is the
SUM() function which makes it easy to add the
contents of a range of cells together.
• For example, if you entered the formula =SUM(
D2:D6) in cell D7 of a worksheet, the result of
adding the contents of the cells from D2 to D6
would be calculated and displayed.
• This is much easier than having to type in
• Because the SUM() function is so useful, it has
its own button on the standard toolbar.
• Another function is the COUNT() function which
counts how many entries there are in a range of
cells. For example if you entered the
formula =COUNT(B4:B18) in cell B19, the
number of non-empty cells between cells B4 and
B18 in column B would be shown in B19. The
complete list of functions can be found from
Excel’s Help facility.
• The next activity allows you to practise using the
functions SUM() and COUNT().
Use the SUM() function in a
• To use the SUM() function in a formula using the
Autosum button on the toolbar:
• 1. Select the cell that is to contain the formula.
Make sure that it is outside the range of cells that
you want to add together.
• 2. Click the Autosum button on the toolbar.
• If the formula is at the bottom of a column of
figures, a dotted rectangle will enclose the
column of figures.
• If the formula is at the end of a row of figures, a
dotted rectangle will enclose the row of figures.
• 3. Press Enter.
• The sum of the range of figures will be
calculated and displayed.
Use the COUNT() function in a
• To use the COUNT() function in a formula
you use a colon (:) to separate the first cell
and the last cell of the range of cells to be
• For example to count the number of
entries in column B from B3 to B13:
• 1. Select the cell that is to contain the
formula, for example B14. Make sure that
it is outside the range of cells that you
want to count.
• 2. Type “=COUNT(B3:B13)”
• 3. Press Enter.
• The number of non-empty cells in the
range B3 to B13 will be counted and
Using the SUM() and COUNT()
• In this activity you practise using the SUM() and
COUNT() functions in two worksheets.
• 1. Open a new worksheet and copy the following
• 2. Use the COUNT() function to count the number
of junior members and senior members.
• 3. Use the SUM() function to add the membership
fees in the juniors column and in the seniors
• 4. Use a formula to add and display the total
number of members and the total membership
• The following illustration shows what the
new worksheet should look like after
making the above modifications.
• 5. Save new worksheet as “members01.xls”.
• 6. Open “shopping list01.xls”.
• 7. Use the SUM() function to display the sums of
columns D, E and F as shown below.
• 8. Save the new worksheet as “shopping list02.xls”.