Excel Practice
Lessons Lesson 4

The SUM Function

About this lesson

SUM adds up every number you give it. That can be a range with a colon, or a list of cells with commas.

By the end you can

  • total a column with SUM instead of typing plus signs.
  • add cells that are not next to each other with a comma.
  • explain why a SUM is too low when a cell holds text.

Practises SUM SUMIF COUNT

The idea

Adding 4 cells with plus signs gives the same answer as SUM. Adding 400 cells does not, because nobody types 400 plus signs. SUM is also useful because of what it ignores. It ignores empty cells and it ignores text. So a column with a gap or a note in it still adds up.

This has a cost. A number with an extra space, or a number pasted from a website, is stored as text. SUM leaves it out and gives no warning. When a total looks too low and every cell looks right, one of the numbers is probably text.

The mistake to watch for

A number stored as text inside the range. SUM skips text and gives no warning. So one number that came from another system as text drops out of the total. The total still looks normal. When a SUM looks too low, look for a number on the left side of its cell. That is a sign it is text.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

SUM adds up every number in a range.

In B7, type =SUM(B2:B6).

SUM is the function. The brackets hold what it works on. B2:B6 is the range from B2 down to B6: the five hours.

B7
Row A B C D E F G
1 Staff Hours
2 Alina 7.5 Last three staff
3 Marcus 9
4 Priya 6.25 Late shift
5 Tomas 8
6 Yusuf 5.75 Alina, Marcus, Yusuf
7 Total
8

Every step

  1. 1

    SUM adds up every number in a range. In B7, type =SUM(B2:B6). SUM is the function. The brackets hold what it works on. B2:B6 is the range from B2 down to B6: the five hours.

    Hint. Equals, SUM, bracket, B2:B6, bracket.

  2. 2

    Read =SUM(B2:B4) and say what it returns. The range stops three rows in.

    Hint. Three rows, three numbers.

  3. 3

    Now write one yourself. In E2, total the hours of the last three on the list: Priya, Tomas and Yusuf.

    Hint. The range starts at B4.

  4. 4

    A colon means "everything between". A comma means "and also", and it lets SUM add cells that are nowhere near each other. In E4, total Marcus and Tomas, rows 3 and 5, by listing the two cells with a comma between them.

    Hint. B3, a comma, B5.

  5. 5

    In E6, write a formula with SUM that totals the hours for Alina, Marcus and Yusuf. Two of them are next to each other. One is not. SUM accepts a range and a single cell in the same brackets.

    Hint. A range, a comma, a cell.

  6. 6

    Priya was off, and somebody typed the words off sick into B4 instead of a number. B7 still holds =SUM(B2:B6). What does it show now?

    Hint. Four numbers, one line of text.