Sum a Column
Click the cell below a column of numbers and press Alt+= (Cmd+Shift+T on a Mac) — AutoSum writes the =SUM() formula over the range above it for you. Type it yourself as =SUM(A2:A100) when you want to control the range: SUM ignores text and blank cells, and the range stretches on its own when you insert a row inside it, which a hand-written chain of =A2+A3+A4 never does. If the total comes back as 0, the numbers are almost certainly stored as text rather than as numbers.
The formula
=SUM(A2:A100) add up a range, ignoring text and blank cells
=SUM(A:A) the whole column — keep it out of column A or it references itself
=SUM(Sheet1:Sheet3!B2) 3-D reference: B2 totalled across every sheet from Sheet1 to Sheet3
=SUBTOTAL(109,A2:A100) total only the rows a filter has left visible
=SUM($B$2:B2) running total — drag it down and the range grows one row per stepA worked example
B2:B13 holds twelve monthly sales figures and B14 is empty.
=SUM(B2:B13)
The twelve months added together in B14. Insert a new month anywhere between rows 2 and 13 later and the formula widens to =SUM(B2:B14) by itself, where a hand-typed =B2+B3+…+B13 would keep ignoring the new row until someone noticed.
Which one do I need?
| If you want to… | Use |
|---|---|
| Adding up a plain column or row of numbers | Select the cell just below (or just right of) them and press Alt+= for AutoSum, or type =SUM(A2:A100) |
| Adding up only the rows a filter has left visible | =SUBTOTAL(109,A2:A100) — a plain SUM counts the hidden rows too |
| Adding up only the rows that meet a condition | =SUMIF(A2:A100,"North",B2:B100) for one test, SUMIFS for several at once |
| Adding the same cell up across several sheets | =SUM(Sheet1:Sheet3!B2), which also picks up any sheet you later drag between those two tabs |
| Putting a total row under an Excel table | Click inside the table, then Table Design > Total Row — the total moves down as rows are added |
| Counting the entries rather than adding them up | =COUNT(A2:A100) for numbers only, =COUNTA(A2:A100) for anything non-blank |
Frequently asked questions
Why does my SUM formula return 0?
The values are text that looks like numbers — typically imported or pasted, left-aligned by default and often carrying a small green triangle. SUM skips text entirely, so it adds nothing and reports 0. Select the column and run Data > Text to Columns > Finish to convert it in place. Two rarer causes: calculation is set to Manual under Formulas > Calculation Options, or the formula cell itself is formatted as Text, in which case it displays the formula instead of a result.
Is =SUM(A1:A10) better than =A1+A2+A3?
For more than two cells, yes. SUM ignores text and blanks where a + chain returns #VALUE! the moment one cell holds text, and a range widens automatically when a row is inserted inside it while the + chain silently keeps skipping that row. The + operator only wins when the cells are scattered and unrelated, and even then =SUM(A1,C5,F9) handles it.
How do I add up cells by their fill colour?
Excel has no function that reads a fill colour. The practical route is to filter the range by colour (right-click a coloured cell > Filter > Filter by Selected Cell's Color) and put =SUBTOTAL(109,range) underneath, since SUBTOTAL only counts what the filter leaves visible. The alternative is a helper column tagging each row's colour by hand, then a SUMIF on that column.
Why does =SUM(A:A) give a circular reference warning?
A whole-column reference includes every cell in that column, including the one holding the formula, so the formula is part of its own input. Move the total into another column, or bound the range as =SUM(A2:A1000). The same trap catches =SUM(A:A) placed in A1 as a "grand total" header.
New guides and tools, once a month
DE + EN · double opt-in · no spam