How to sum a column in Excel
Draft. Not published yet.
Click the empty cell under the numbers, press Alt+= (or select AutoSum on the Home tab) and press Enter. Excel writes =SUM( ) with the cells above and shows the total.
Part of the Math and trig functions guide.
Method 1: Use the AutoSum button
Ribbon
- Click the empty cell under the numbers, for example
A5. - Select Home > Editing > AutoSum.
Drawing to come: The Home tab with AutoSum in the Editing group - Check the cells Excel picked: the cell shows
=SUM(A2:A4), leaving out the Sales header. - Press Enter.
A5 shows 60, the total of 10, 20 and 30.
Method 2: Press Alt+=
Keyboard
- Click the empty cell under the numbers, for example
A5. - Press Alt+=.
- Press Enter.
The same =SUM(A2:A4) as the button, and the same total: 60.
Method 3: Type the SUM formula
Formula
Use this when the total goes somewhere other than straight under the numbers.
- Click the cell that should show the total.
- Type
=SUM(. - Select the cells to add, for example
A2:A4. - Type
)and press Enter.
The cell shows the total, and the formula bar shows =SUM(A2:A4).
What goes wrong
The total is smaller than it should be
SUM skips a number stored as text. With A3 holding 20 as text, =SUM(A2:A4) returns 40. Set the cell’s format back to General and type the number again; the total goes back to 60.
SUM shows #VALUE!
That happens when text goes straight into the formula, as in =SUM("a",3). Text in the cells you select is skipped instead; text typed between the brackets is an error.
What Excel returns
We ran these in desktop Excel (build 16.0.20527) and recorded what each cell showed.
| Formula | Excel shows | Note |
|---|---|---|
=SUM(1,2,3) | 6 | Three numbers typed straight into the formula. |
=SUM("a",3) | #VALUE! | Text typed straight into the formula is an error. |
Try it
Live grid coming soonYour turn: In A5, write a formula that adds A2 to A4.
Functions used
Questions people ask
Does SUM add the header at the top of the column?
No. AutoSum and Alt+= start below the header, and SUM skips text in the cells it adds.
Why is the total smaller than it should be?
A number stored as text is skipped. With 20 stored as text, =SUM(A2:A4) over 10, 20 and 30 gives 40, not 60.
Part of the Math and trig functions guide.