SUM
Adds every number you give it: cells, ranges, or typed values, in any mix.
SUM is usually the first real formula anyone learns in Excel, and it stays one of the most-used for a reason: almost every report ends with a total somewhere. It adds a range, a handful of typed numbers, or several of each mixed together, and it quietly skips anything in the range that isn't a number rather than throwing an error.
That quiet skipping is convenient most of the time and a trap exactly once: it means a typo like 1O (a letter O instead of a zero) in a numeric column doesn't show up as an error. The total just comes out slightly low, with nothing on screen to flag why.
Syntax
| Argument | Description |
|---|---|
| number1 | Required. A number, a cell (B2), or a range (B2:B6). |
| number2, ... | Optional. Up to 255 more numbers, cells, or ranges to add to the total. |
A real example: a weekly hours total with a flat fee added in
Say a freelance invoice adds up billable hours from a range, plus a flat $45 travel fee that isn't its own row:
Mixing a range with a typed number in the same SUM call is exactly what this is for: a range for the recurring data, plus a one-off number that doesn't deserve its own row in the table.
Worked examples
| A | B | |
|---|---|---|
| 1 | Item | Qty |
| 2 | Pens | 12 |
| 3 | Notebooks | 4 |
| 4 | Folders | 7 |
| A | B | |
|---|---|---|
| 1 | Ticket | Price |
| 2 | Fri | 18 |
| 3 | Sat | 22 |
(the 5 covers a booking fee that isn't its own row)
Common mistakes
- Leaving out a row when the range grows. SUM doesn't auto-expand, so reselect the range.
- Summing a column that has text mixed in, expecting an error. Excel just ignores the text, which can hide a typo like "1O" instead of "10".
- Using SUM where you meant COUNT or COUNTA. SUM totals values, it doesn't count entries.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice SUM →