To sum a column in Excel, click the cell under the numbers and type =SUM(B2:B6), then press Enter. The formula adds every number from B2 to B6, and the total changes whenever one of those numbers changes.
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 1200 |
| 3 | Feb | 950 |
| 4 | Mar | 1430 |
| 5 | Apr | 1100 |
| 6 | May | 1320 |
| 7 | Total | 6000 |
Click B7 to see the formula and the range it adds, outlined on the grid. Change Feb to 2000 and the total follows. B2:B6 is a range: the first cell, a colon, the last cell.
AutoSum: the shortcut for SUM
You rarely need to type SUM. Click the empty cell under a column of numbers and press Alt+= (on a Mac, Cmd+Shift+T), or click Home > AutoSum. Excel guesses the range above the cell, shows it with a moving border, and writes =SUM(B2:B6). Press Enter to accept it, or drag over other cells first to fix the guess.
AutoSum stops at the first empty cell or the first cell with text, so a gap in the column gives a range that is too short. Check the outlined range before you press Enter.
To see a total without writing any formula, select the cells: the status bar at the bottom of the Excel window shows Sum, Average and Count for the selection.
SUM formula syntax
=SUM(number1, [number2], ...)
Each argument can be a range (B2:B6), a single cell (B2), a number (100) or another formula. Up to 255 arguments are allowed, and SUM adds all of them. In Excel set to a language that uses a decimal comma, arguments are separated by semicolons: =SUM(A1;A2).
How to sum a row in Excel
A row works the same way, with the range running across: =SUM(B2:D2). Here the formula is written once in E2 and filled down, so each row adds its own three months.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | Q1 total |
| 2 | North | 420 | 380 | 510 | 1310 |
| 3 | South | 310 | 290 | 335 | 935 |
| 4 | East | 505 | 470 | 490 | 1465 |
| 5 | West | 260 | 315 | 300 | 875 |
Click E3: the formula there is =SUM(B3:D3). Excel moved the row number when the formula was copied down, which is what you want here. To fill a formula down yourself, drag the small square at the bottom corner of the cell, or select the cells and press Ctrl+D (Cmd+D on a Mac).
Sum non-adjacent cells
To add cells that are not next to each other, separate them with commas inside SUM. Hold Ctrl (Cmd on a Mac) while you click the cells and Excel types the commas for you.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Q1 | Q2 | Shirts and socks | Q1 and Q2, first two rows | |
| 2 | Shirts | $2,400 | $2,650 | $3,550 | $6,610 | |
| 3 | Hats | $800 | $760 | |||
| 4 | Socks | $1,150 | $1,300 | |||
| 5 | Belts | $640 | $700 | |||
| 6 | Bags | $1,900 | $2,100 |
E2 adds two single cells and gives 3550. F2 adds two ranges, B2:B3 and C2:C3, and gives 6610. You can mix ranges, cells and plain numbers in one SUM.
Sum across sheets
Name the sheet before the range, with an exclamation mark: Jan!B2:B4 means B2:B4 on the sheet called Jan. Use the tabs under this sheet to see the two monthly sheets.
| A | B | |
|---|---|---|
| 1 | Store | Jan + Feb |
| 2 | All stores | 25500 |
The total is 25500. If the sheet name has a space, put it in single quotes: ='Jan 2026'!B2.
When the sheets have the same layout, a 3D reference adds the same range on every sheet between two tabs:
=SUM(Jan:Mar!B2:B4)
This adds B2:B4 on Jan, Mar and every sheet between them in the tab order. Moving a sheet out of that span removes it from the total.
Why SUM ignores numbers stored as text
A number typed with a leading apostrophe, or imported from another program, can be stored as text. It looks like a number, but SUM skips it without any warning. In Excel such a cell usually shows a small green triangle in its corner.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Amount | SUM | Plus signs | Converted | |
| 2 | 1001 | 250 | 725 | 1125 | 1125 | |
| 3 | 1002 | 400 | ||||
| 4 | 1003 | 175 | ||||
| 5 | 1004 | 300 |
B3 holds the text "400". SUM gives 725 because it leaves B3 out. When every cell in the range is text, SUM returns 0. The plus signs give 1125 because + converts text that looks like a number. =SUMPRODUCT(B2:B5*1) also gives 1125: multiplying by 1 turns every cell into a number first.
The lasting fix is to convert the cells: select them, click the warning icon, and choose Convert to Number. Converting text to numbers covers the other ways.
Practice: total a table
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 | Q4 | Grand total | |
| 2 | North | 420 | 380 | 510 | 450 | ||
| 3 | South | 310 | 290 | 335 | 360 | ||
| 4 | East | 505 | 470 | 490 | 520 | ||
| 5 | West | 260 | 315 | 300 | 280 |
Your turn: In G2, write one formula that adds the sales of all four regions in all four quarters.
A range can cover several columns at once: its first cell is the top corner of the block and its last cell is the opposite corner.
SUM with an error in the range
If any cell in the range holds an error such as #N/A or #DIV/0!, SUM returns that error instead of a total. Find the cell and fix it, or, when the errors are expected, total around them:
=AGGREGATE(9,6,B2:B10)
The 9 means sum and the 6 means ignore error values. To sum only the rows that match a condition, such as one region or one month, use SUMIF.
Frequently Asked Questions
How do I sum a column in Excel?
Click the cell under the numbers and type =SUM(B2:B6), using your own first and last cell, then press Enter. Or click that cell and press Alt+= (Cmd+Shift+T on a Mac), and AutoSum writes the same formula for you.
How do I sum a row in Excel?
Give SUM a range that runs across: =SUM(B2:D2) adds B2, C2 and D2. Fill it down and each row adds its own cells: the copy in row 3 is =SUM(B3:D3).
How do I sum the same cell across several sheets?
Use a 3D reference: =SUM(Jan:Mar!B5) adds B5 on every sheet from Jan to Mar, including the sheets between them. For two named sheets, list them: =SUM(Jan!B2:B4,Feb!B2:B4).
How do I sum an entire column in Excel?
=SUM(B:B) adds every number in column B, however many rows you add later. Put the formula in another column, because a SUM of column B written inside column B refers to itself and causes a circular reference.
What is the difference between SUM and the + sign?
=B2+B3+B4 and =SUM(B2:B4) give the same total on plain numbers. SUM skips text and empty cells, while + converts a number stored as text and returns #VALUE! on real text such as "n/a".
