The SUM function

Adds numbers, cell references and ranges. Text and blank cells in a range are ignored.

How to use SUM

SUM adds up numbers, the way a cashier adds up the items on a receipt. Point it at a column of sales, a row of monthly costs or a few separate cells, and you get one total.

Reach for it whenever you need a total: an invoice, the week's takings, the budget for the quarter. =SUM(C2:C6) adds everything from C2 down to C6. Cells that are not next to each other go in separated by commas, like =SUM(C2:C3,C5). You can mix single cells, ranges and plain numbers in the same formula.

SUM only adds real numbers. Text and empty cells inside a range are skipped without any error message. That is handy on a list with gaps, but it can also hide a problem: a number that is stored as text is left out of the total.

To add only the rows that meet a condition, such as the North region, use SUMIF for one condition or SUMIFS for several. =SUMIF(A2:A6,"North",C2:C6) adds the sales from the rows where column A says North. If you need to multiply first and add afterward, like quantity times price, use SUMPRODUCT.

Syntax

SUM(number1, [number2], ...)
number1
The first number, cell or range to add.
number2 (optional)
More numbers or ranges, up to 255 in all.

Examples

E1=SUM(C2:C6)
ABCDE
1RegionProductSales1650
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180
E1=SUM(C2:C3,C5)
ABCDE
1RegionProductSales1220
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180

In plain words: Add up the sales in C2:C3 and the sales in C5.

Common SUM mistakes

  • My SUM gives 0 or a total that is too low

    The numbers are probably stored as text, often after you paste or import data. They sit on the left side of the cell, and SUM skips them. Make a helper column with =VALUE(C2) and add up that column instead. Also look for an apostrophe in front of the number or a space after it.

  • New rows I add below the list are not in the total

    The range stops at the last row you had when you wrote the formula. Insert new rows inside the range, above the last row, and the formula grows with them. You can also use a longer range such as =SUM(C2:C1000) and keep the total outside it.

  • Excel warns about a circular reference

    The SUM cell sits inside the range it is adding, so it tries to add itself. Move the formula outside the range, for example below the last row, or shorten the range so it stops above the total.

  • =C2+C3+C4 gives #VALUE!

    One of the cells holds text such as n/a, and plus cannot add text. SUM skips the text and adds the rest, so use =SUM(C2:C4) instead.

Practice SUM

Read the guide

Questions

What is the shortcut for SUM in Excel?

Alt+= (Cmd+Shift+T on a Mac) inserts AutoSum, which writes =SUM() around the numbers above or to the left.

How do I sum a whole column in Excel?

Use =SUM(C:C) to add every number in column C, new rows included. Do not put the formula in column C itself, or you get a circular reference.

How do I sum only the visible rows after filtering?

SUM also adds the rows a filter hides. Use =SUBTOTAL(109,C2:C6) instead, which skips hidden and filtered-out rows.

How do I sum across several sheets?

In Excel, a 3D reference adds the same cell on a run of sheets: =SUM(Jan:Mar!C2) adds C2 on the sheets from Jan to Mar. Google Sheets has no 3D references, so add each sheet separately: =Jan!C2+Feb!C2+Mar!C2.

How do I sum only some of the numbers?

Use SUMIF when one condition decides: =SUMIF(A2:A6,"North",C2:C6). Use SUMIFS when you have two or more conditions.

Related functions