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

By Ali, Founder, Excel Lessons

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.

Read more about this

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)

The brackets hold the range, B2 down to B6.

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). The brackets hold the range, B2 down to B6.

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

  2. 2

    Read =SUM(B2:B4) and say what it returns. The range stops at row 4.

    Hint. Three rows, three numbers.

  3. 3

    In E2, total the hours in B4:B6 with SUM. Shape: =SUM(range).

    Hint. Equals, SUM, the range in brackets.

  4. 4

    A colon means "everything between". A comma means "and also", so SUM can add cells that are apart. In E4, total B3 and B5 with SUM. Shape: =SUM(cell,cell).

    Hint. B3, a comma, B5.

  5. 5

    SUM accepts a range and a single cell in the same brackets. In E6, total B2:B3 and B6 with SUM. Shape: =SUM(range,cell).

    Hint. A range, a comma, a cell.

  6. 6

    Somebody typed the words off sick into B4 instead of a number. B7 still holds =SUM(B2:B6). Say what B7 shows now.

    Hint. Four numbers, one line of text.