SUM in Excel: how to use it
SUM adds up whatever you select, such as =SUM(C2:C6). Text and empty cells are simply skipped.
Try the first exercises for freeIn the lesson The SUM function
What does SUM do?
SUM adds numbers together. You give the function a range, such as C2:C6, and Excel adds up all the numbers in those cells. You can also give separate cells or numbers, with a comma in between: =SUM(C2,C4,100).
SUM skips text and empty cells. If a number in the range changes, Excel recalculates the total straight away.
When do you use SUM?
- The total of a column of amounts, such as a month's revenue: =SUM(C2:C31).
- All the hours worked in a week: =SUM(B2:B6).
- A total row under a table, or a total column next to it: =SUM(B2:D2) adds up a row.
- Cells that aren't next to each other, such as three monthly totals: =SUM(B10,D10,F10).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =SUM(C2:C6)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
SUM | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | SUM |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
number1 | A cell, a range or a number. | C2:C6 |
[number2] | Optional: anything else to add, separated by a comma. | left out |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. | |
... | You can give more, with a comma between each one. |
SUM step by step
E1 contains =SUM(C2:C6). Adds up the revenue of all five rows. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click the cell where the total should go, here E1.
- Type an equals sign, the name of the function and a bracket.
=SUM( - Use the mouse to select the cells you want to add up, from C2 to C6. Excel writes the range into the formula for you.
=SUM(C2:C6 - Close the bracket and press Enter. 3005 appears in E1.
=SUM(C2:C6)
More examples
Adding up separate cells
E1 contains =SUM(C2,C4). With a comma, you add up cells that aren't next to each other: here the two revenue figures from Leeds.
Two columns at once
E1 contains =SUM(B2:C4). A range can be more than one column wide: B2:C4 adds up January and February together.
Text in the range
D1 contains =SUM(B2:B5). SUM skips the cell with ill. With plus signs, such as =B2+B3+B4+B5, you'd get #VALUE!.
SUM or plus signs?
=C2+C3+C4 works too, but if you insert a row in the middle, it stays out of your total. A range stretches along automatically. SUM also skips text in a range, whereas a plus sign gives #VALUE!.
A quick total
Go to the empty cell below a column of numbers and press Alt + =. Excel writes the SUM formula for you and selects the cells above. Check the selection and confirm with Enter. On a Mac, use Cmd + Shift + T.
Adding up several ranges
The ranges don't have to sit next to each other. =SUM(B2:B10,D2:D10) adds up two columns and skips column C. You can also add a single number, such as =SUM(B2:B10,50).
The same cell on several worksheets
If you have a sheet per month with the same layout, =SUM(January:March!B2) adds up cell B2 on all those sheets, including the ones in between.
Only adding up visible rows
If your list is filtered, SUM still counts the hidden rows. With =SUBTOTAL(109,B2:B50) you only add up what you see on screen.
Where it often goes wrong
- Numbers stored as text aren't counted by SUM. You can spot them because they're aligned to the left.
- Don't put the formula cell inside its own range: that gives a circular reference.
- SUM also counts rows you've hidden with a filter.
Practise this in the lesson The SUM function. The first exercises are free.
Frequently asked questions
What's the shortcut for SUM?
Alt + = on Windows, Cmd + Shift + T on a Mac. Check afterwards that Excel picked the right cells.
Can I add up a whole column?
Yes, with =SUM(C:C). Just don't put that formula in column C itself.
Read next
Subtract, multiply, divideAVERAGEMIN and MAXABS, POWER and SQRT