The Excel SUM function, with practice exercises
SUM adds up every number in one or more ranges. For example six months of expenses, or the revenue of all branches over a quarter.
In short: =SUM(B2:B7) adds every number from B2 to B7. Separate several ranges with commas: =SUM(B2:B7,D2:D7). Text and blank cells inside the range are ignored.
Try it now
The sheet lists monthly office supply expenses. Work out the total for the first half of the year. Click the yellow cell and write the formula. Clicking or dragging over cells while you type inserts their reference.
Task In B9, use SUM to calculate the office supply expenses for the first half of the year.
| A | B | |
|---|---|---|
| 1 | Month | Expenses $ |
| 2 | January | 1240 |
| 3 | February | 980 |
| 4 | March | 1105 |
| 5 | April | 1320 |
| 6 | May | 1015 |
| 7 | June | 1190 |
| 8 | ||
| 9 | Total: |
The SUM formula
| Argument | Meaning | In the example |
|---|---|---|
| number1 | The first range, a single cell or a number. Required. | B2:B7 |
| number2, number3 … | More ranges, cells or numbers, separated by commas. Optional, up to 255 arguments in total. | not needed |
The colon in B2:B7 reads as "B2 through B7", so Excel takes all six cells in between and =SUM(B2:B7) returns 6,850. A range can also span several columns. B2:D6 is the block from B2 at the top left to D6 at the bottom right.
One more: a whole block
Now five branches report revenue for three months side by side. You need the quarter total for all of them. One block reference does it, or you can list the columns as separate ranges.
Task In F2, calculate the revenue of all branches in the first quarter, January to March combined.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Branch | January $ | February $ | March $ | Q1 all branches | |
| 2 | Berlin | 12400 | 11800 | 13100 | ||
| 3 | Hamburg | 9800 | 10200 | 9500 | ||
| 4 | Munich | 14100 | 13600 | 15000 | ||
| 5 | Cologne | 8700 | 9100 | 8900 | ||
| 6 | Leipzig | 6400 | 6900 | 7200 |
AutoSum with Alt+=
You rarely need to type SUM yourself. Select the empty cell below a column of numbers and press Alt + = on Windows. Excel inserts =SUM(…) with the range above already filled in, and Enter accepts it. The same command sits behind the Σ button on the Home tab.
Check the suggested range before you accept it. AutoSum stops at the first blank or text cell going up, so a gap in the middle of a column means only the lower part is added. If you select a whole block plus the empty row below it, AutoSum writes every column total in one go.
AutoSum, Fill Down and F4 together save a lot of typing, and you can practice all three on Excel editing shortcuts.
For a quick look without any formula, select the cells and read the sum in the status bar at the bottom right of the window.
SUM or the plus sign?
=B2+B3+B4 gives the same result as =SUM(B2:B4), and for two or three cells that is fine. For anything longer, SUM wins:
- The formula stays short whether the range has 6 rows or 600.
- Insert a row inside
B2:B7and the range grows with it. A chain of plus signs misses the new row. - A text value in one of the cells makes the plus chain return #VALUE!. SUM simply skips it.
The plus sign is still handy for a few scattered cells such as =B9+F2, and you can combine both: =SUM(B2:B7)+F2.
Relative references when you fill right
Once you have a total under column B, you do not need to write new formulas for columns C and D. Drag the fill handle, the small square at the bottom right of the cell, to the right. =SUM(B2:B7) becomes =SUM(C2:C7) and then =SUM(D2:D7). References without dollar signs are relative: Excel stores "the six cells above me", not fixed addresses. If the range must stay put when you copy, make it absolute: =SUM($B$2:$B$7).
Common SUM mistakes
Numbers stored as text
The total is too low although every value is visible. Some cells probably hold numbers as text, which often happens after importing data from another system. They are usually left aligned and show a small green triangle. SUM skips them without any warning. Select the cells, click the warning icon and choose "Convert to Number".
The range is too small
SUM only counts what is inside its range. Add new months below the list and =SUM(B2:B7) still looks at six cells, so the total is wrong. Insert new rows inside the range or extend it. An Excel table (Ctrl + T) makes this easier, because its total row includes new rows automatically.
Circular reference
If the formula sits in B9 and its range runs to B9, as in =SUM(B2:B9), the cell is adding itself. Excel warns about a circular reference and usually shows 0. Let the range end above the formula cell: =SUM(B2:B7).
SUM in German Excel
In a German Excel the function is called SUMME, and arguments are separated by semicolons because the comma is the decimal separator there:
| English | German |
|---|---|
=SUM(B2:B7) | =SUMME(B2:B7) |
=SUM(B2:B6,D2:D6) | =SUMME(B2:B6;D2:D6) |
| AutoSum | AutoSumme |
| SUMIF | SUMMEWENN |
Our exercises accept both spellings. Pick your Excel language below each exercise.
Which Excel versions have SUM?
All of them, including Excel 2016 and 2019, Excel for Mac and Excel for the web. AutoSum is available everywhere too. To add only some rows, for example revenue from one city, use SUMIF.
Frequently asked questions
Can SUM add an entire column?
Yes, with =SUM(B:B). Just do not put that formula in column B itself, or you create a circular reference. Headers do no harm because SUM ignores text.
How do I add the same cell across several sheets?
Use a 3D reference: =SUM(January:March!B2) adds B2 from every sheet from January to March. The sheets have to sit next to each other.
Why does my SUM return 0?
Usually every number is stored as text, or the formula refers to its own cell. Check whether the values are left aligned and whether the range includes the formula cell.