FormulaGym
XP 0
Streak 0

The SUM function in Excel

SUM adds numbers together. It is the first function most people learn, and the one you will use most often.

What SUM does

Give SUM a range of cells and it returns their total. It skips text and empty cells, so a stray note in the column will not break the answer. You can also pass several ranges or single numbers, separated by commas.

Syntax

=SUM(number1, [number2], ...)
number1
The first number, cell or range to add, such as B2:B6.
number2, ...
Optional. More numbers, cells or ranges to add to the total.

Worked example

AB
1DaySales
2Mon120
3Tue95
4Wed140
5Thu110
6Fri160
7Total625
B7 =SUM(B2:B6)

SUM adds every number from B2 to B6. If Wednesday's figure changes, the total updates on its own.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Finance

Total a column of invoices or expenses.

Sales & Ops

Add up units shipped or sold for the week.

HR

Total the overtime hours across a team.

Common mistakes

  • Typing the numbers into the formula (=120+95+140). It will not update when the data changes. Point at the cells instead.
  • A range that stops short. =SUM(B2:B5) misses row 6 if your data runs to B6. Check where the range ends.
  • Numbers stored as text. SUM ignores them, so the total comes out too low. They are often left-aligned or marked with a green triangle.

The SUM course

  1. 1
    Add up a columnFree practice below
    Practise
  2. 2
    Add several ranges togetherComing soon
  3. 3
    Totals that ignore text and blanksComing soon
  4. 4
    Running totals with locked referencesComing soon
  5. 5
    Subtotals for each sectionComing soon
  6. 6
    Fix a total that misses rowsComing soon

Common questions

What is the difference between SUM and the plus sign?

The plus sign adds cells one at a time and returns an error if one contains text. SUM accepts whole ranges and skips text, so it is shorter and safer.

Is there a keyboard shortcut for SUM?

Yes. AutoSum inserts a SUM formula for you. On Windows press Alt and equals. On Mac press Command, Shift and T.

Related functions