SUM
Adds up numbers from cells and ranges: quarterly sales, total expenses, the size of an order.
SUM(number1, [number2], ...)- [ ]optional part
- number1
- The first thing to add: a cell, a range like B2:B10 or a number.
- number2
- More cells or ranges, separated by commas. Optional.Optional
Return value
A number: the sum of all the numbers. Text and empty cells in the range are skipped.
Examples
Quarterly sales
fx
A
B
C
D
E
F
1
2
3
4
5
6
7
Rep
January, $
February, $
March, $
Quarter, $
Johnson
184000
172500
205300
Miller
156200
149800
163400
Davis
201700
188900
214600
Wilson
98400
121300
134700
Sheet1
Details
SUM accepts cells, ranges and plain numbers separated by commas. The range B2:D2 means every cell from B2 to D2.
SUM skips empty cells and text inside a range. So a number that Excel stores as text won’t make it into the total.
A quick way to insert SUM: select the cell under a column of numbers and press Alt and = at the same time. Excel suggests a range on its own, so check it with your eyes.
Common mistakes
- =SUM(B2,B6) adds only two cells, while =SUM(B2:B6) adds all five. A comma lists cells, a colon defines a range.
- SUM silently skips numbers stored as text, so the total comes out smaller. Such cells are usually aligned to the left.
- The cell with the formula itself is inside the range: Excel warns about a circular reference and the total isn’t calculated.