SUM in Excel: what it does and how to use it

SUM adds up everything in the range you give it, ignoring any text and empty cells along the way.

Syntax

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

When to use it

Every total. It also powers two common jobs: a running total, and each row shown as a share of the whole. Both work by anchoring one end of the range with dollar signs.

What trips people up

The dollar signs are the part worth learning. $B$2 stays put when the formula is filled down, while B2 moves with it. A running total needs one end pinned and the other free.

Watch it built, step by step

Build a running total — Where the dollar signs come in
Each row as a share of the total — Percentages that add to 100

Questions

Why is my SUM lower than it should be?

Some of the numbers are stored as text, and SUM skips text without warning. They usually sit on the left of the cell instead of the right.

What is the difference between SUM and adding with +?

=A1+A2 returns #VALUE! if either cell holds text. =SUM(A1:A2) skips the text and adds the rest.

How do I total a column that keeps growing?

Use the whole column, =SUM(B:B), in a cell outside column B. Or turn the data into a table, and a total such as =SUM(Table1[Amount]) grows with it.

Related functions

SUMIF — SUMIF adds up the numbers in a column, but only on the rows that meet one condition you set.
SUMPRODUCT — SUMPRODUCT multiplies two ranges together row by row, then adds up all the results, in one step.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.