Home › Excel functions › SUM

SUM Formula in Excel: SUM Function, AutoSum and Shortcuts

SUM adds up numbers. It's the first function most people learn, but there's more to it than =SUM(A1:A10): shortcuts, sums across sheets, and the reasons a total sometimes comes out wrong.

Excel CSE · 3 min read

SUM syntax

Each argument can be a number, a cell, or a range. You can give up to 255 arguments.

Basic example

This adds up the four months: 4,685. Empty cells and text in the range are simply skipped.

The AutoSum shortcut

Select the empty cell below (or to the right of) your numbers and press:

• Windows: Alt+=
• Mac: Cmd+Shift+T

Excel writes the SUM formula and guesses the range. Check the highlighted range and press Enter. You can also select a block of numbers plus an empty row below it and press the shortcut once to get a total under every column.

Add up several ranges or cells

• Separate ranges: =SUM(B2:B5, D2:D5)
• Individual cells: =SUM(B2, B4, F7)
• Numbers and cells mixed: =SUM(B2:B5, 500)
• A whole column: =SUM(B:B) includes new rows automatically. Don't put this formula in column B itself, or you'll create a circular reference.

Sum the same cell across sheets

If you have one sheet per month with the same layout, you can add up the same cell on all of them with a 3D reference:

This adds B6 on every sheet from "January" to "March", including any sheets in between. Sheet names with spaces need quotes: =SUM('Week 1:Week 4'!B6).

Sum with conditions

To add up only some of the numbers, for example only one region's sales, use SUMIF or SUMIFS. To add up only the rows that are visible after filtering, use SUBTOTAL.

When the total is wrong

• The total is too low. Some numbers are stored as text. They're usually left-aligned and may show a small green triangle. SUM skips them. Convert them with Paste Special › Add or by multiplying by 1. All methods: convert text to numbers.
• The result is an error like #N/A. One of the cells in the range contains an error. Fix that cell, or use =AGGREGATE(9, 6, B2:B5) to sum while ignoring errors.
• The total shows 0 or a warning about a circular reference. The formula's own cell is inside the range it adds up.
• The total doesn't update. Calculation is set to manual. Go to Formulas › Calculation Options › Automatic, or press F9.
• The total looks off by a cent. The cells show rounded values but contain more decimals. See ROUND.