SUM
Add up the numbers.
Add up a column of figures
The total of the numbers in the range
Syntax
=SUM(range1, [range2], ...)
| Argument | Required | What goes here |
|---|---|---|
range1 | Required | The cells to add, like C2:C13. |
range2 | Optional | The cells to add, like C2:C13. |
How to use SUM
SUM adds the numbers in whatever you give it and quietly ignores anything that is not a number: text, blanks and headings all pass through without complaint.
That silence is the thing to watch. A column where three amounts arrived as text totals without an error and comes out short, and nothing on the sheet says so.
A total belongs directly under its column with a label beside it. Somebody has to be able to see what it is the total of.
Alt and the equals key writes the whole formula and guesses the range, which is right more often than not and worth checking either way.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=SUM(E2:E9)The ledger total. | 7850 |
=SUM(E2:E5)+SUM(E6:E9)Two halves adding back to the whole, which is how a total gets proved rather than trusted. | 7850 |
=SUM(A2:A9)A column of invoice numbers, which are text. SUM finds nothing to add and says nought rather than complaining. | 0 |
=SUM(E2:.E40)The same total written over a generous block so next month can be pasted underneath. The dot trims the empty rows off the end before the range is read. | 7850 |
Notes
- SUM ignores text and blanks. Use COUNT against COUNTA to find out whether it should have.
- Adding cells one at a time with plus signs gives the same answer and stops working the moment a row is inserted.
Related
A reference tells you what it does. A unit makes you use it.