What is the difference between SUM and the plus sign?

They give the same answer on clean numbers. SUM skips text and blank cells, while =B2+B3 returns #VALUE! if either cell holds text. SUM is also shorter once there are more than three or four cells.

How do I sum an entire column?

Use =SUM(B:B). It adds everything in column B, so keep the formula in a different column or you create a circular reference. A bounded range such as B2:B500 is easier to audit.

What is the keyboard shortcut for SUM?

Alt+= on Windows inserts AutoSum under the selected numbers. On a Mac the shortcut is Command+Shift+T.

Why does SUM show 0 when my cells have numbers?

The numbers are almost certainly text. They are left-aligned and often have a green triangle. Convert them to numbers and the total appears.

Function guides · Math

SUM function

Adds numbers, cell references and ranges.

Syntax

=SUM(number1, [number2], ...)

Argument	What it is
`number1`	A number, a cell reference, or a range such as B2:B10.
`number2, ...`	Optional. Up to 255 more numbers or ranges.

What SUM does

SUM adds everything you give it. It is the first function most people learn, and the one employer tests use to check that you understand what a range is.

Give it one cell, a block of cells, or a mix of both. Anything that is not a number is skipped: blank cells, text, and logical values inside a range all count as nothing. Errors are different. If one cell in the range shows #N/A, the total shows #N/A too.

The shortcut is AutoSum. Select the cell under the numbers and press Alt+=, and Excel writes the formula for you.

SUM examples

1. Total a column of costs

Total a column of costs. The formula =SUM(B2:B5) in cell B6 returns 1485.
	A	B
1	Item	Cost
2	Rent	1200
3	Power	85
4	Internet	60
5	Insurance	140
6		1485

The range B2:B5 holds four monthly costs. SUM walks down the range and adds every number it finds, so the answer is 1200 + 85 + 60 + 140.

Writing the total as a range rather than =B2+B3+B4+B5 matters later. Insert a new row inside the range and SUM picks it up without any editing.

2. Add two separate ranges

Add two separate ranges. The formula =SUM(B2:B4,D2:D4) in cell B6 returns 2555.
	A	B	C	D
1	Rep	Q1		Q2
2	Dana	410		380
3	Luis	295		340
4	Mei	520		610
5
6		2555

SUM accepts several arguments, and each can be a range of its own. Here the first-half and second-half sales sit in different blocks with a gap column between them, so one range will not do.

The commas simply tell Excel to add the result of the first range to the result of the second.

3. Running total with an anchored start

Running total with an anchored start. The formula =SUM($B$2:B5) in cell C5 returns 446.
	A	B	C
1	Day	Sales	Running
2	Mon	120	120
3	Tue	95	215
4	Wed	143	358
5	Thu	88	446

A running total is SUM with a mixed reference. The start of the range is locked with dollar signs and the end is left alone, so filling the formula down stretches the range one row at a time.

The highlighted cell is the last one in the column and holds the full total. The cells above it hold the same formula for the earlier rows.

Common SUM mistakes

Numbers stored as text. A green triangle in the corner of the cell is the warning. SUM ignores text, so a cell that looks like 250 but is left-aligned adds nothing and the total comes up short. Select the cells and use the warning icon's Convert to Number, or multiply the column by 1 with Paste Special.
A range that stops one row early. AutoSum guesses the range and stops at the first blank cell. If row 14 is empty, it totals only the rows below it. Look at the marching ants before you press Enter.
Typing the numbers into the formula. =SUM(1200,85,60) works, but nobody can see where the figures came from and nothing updates. Point at cells instead of retyping their values.
Including the total in its own range. =SUM(B2:B6) in cell B6 triggers the circular reference warning and shows 0. Keep the total outside the range it adds.
Summing a filtered list and expecting only the visible rows. SUM counts hidden and filtered rows too. To total only what the filter shows, use SUBTOTAL(109, range) instead.

SUM on Excel job tests

Almost every Excel test opens with a total: "calculate the total of column D in D20". Enter it as a formula. A typed 4,512 may be right today and wrong the moment the data changes.

Look at the column before you type anything. Press Ctrl+Down from the first number to see where the data really stops, and scan for numbers stored as text and for subtotal rows sitting inside the range. On a timed test, AutoSum (Alt+=) is fastest, but check the range it picked before pressing Enter.

If the instructions name particular columns ("add only January and March"), list the two ranges inside one SUM, separated by a comma.

SUM questions

What is the difference between SUM and the plus sign?: They give the same answer on clean numbers. SUM skips text and blank cells, while =B2+B3 returns #VALUE! if either cell holds text. SUM is also shorter once there are more than three or four cells.
How do I sum an entire column?: Use =SUM(B:B). It adds everything in column B, so keep the formula in a different column or you create a circular reference. A bounded range such as B2:B500 is easier to audit.
What is the keyboard shortcut for SUM?: Alt+= on Windows inserts AutoSum under the selected numbers. On a Mac the shortcut is Command+Shift+T.
Why does SUM show 0 when my cells have numbers?: The numbers are almost certainly text. They are left-aligned and often have a green triangle. Convert them to numbers and the total appears.

Practice SUM

Try it in a working grid: the free lesson Adding with SUM (module 4 of Excel from Zero) has checked SUM exercises.

Practice this function