Practice free

SUM function

SUM adds numbers together and returns the total of everything you supply. It is the most-used function in Excel. To add only some rows, use SUMIF or SUMIFS.

Example

Read it as a sentence: add up every number from D2 to D7.

Practice this in the app

How SUM works

SUM answers the most common question in a spreadsheet: what do these numbers come to? The total of a month's sales, the hours on a timesheet, the stock on a shelf.

You give it the numbers to add. Each argument can be a single number, a cell or a whole range, and you can mix them, up to 255 arguments in one formula. A colon joins the two ends of a range, as in D2:D7, and a comma separates one argument from the next.

In the example above, D2:D7 is the Qty column of the Items sheet: six rows, from the boxer cartridges down to the commonplace book. Excel adds 12, 7, 20 and the rest, and returns 68.

Cells in a range that hold text or nothing at all are skipped, so a header or an empty row does no harm. The same rule has a catch: a number stored as text is skipped too, which Watch out for shows below.

Syntax

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

Arguments in [square brackets] are optional.

The first number, cell or range number1 (required)
A number, cell or range.
More numbers, cells or ranges [number2], ... (optional)
More numbers, cells or ranges.

Returns: The total of everything supplied.

More examples

Each picture shows only the columns that formula reads.

  1. Add two ranges and a single cell

    What do the cab fares to the first five places and to Pondicherry Lodge come to?

    Separate the pieces with commas. F2:F6 covers the first five places, F10 adds Pondicherry Lodge on its own, and the total is 11.25.

  2. Multiply, then add, in one cell

    What is all the stock worth?

    Multiplying the two ranges gives quantity times unit value for every row, and SUM adds those six results into one total, 242. No helper column needed.

  3. Total a whole column

    What if more items are added later?

    D:D means the entire column, so any row added below the list is in the total straight away. The header, Qty, is text, so it is skipped. The answer, 109, covers every item in the column, not only the six rows above, which is why it differs from the 68 in the first example.

  4. Keep a running total

    How much stock is there down to row 4?

    Lock the start of the range with dollar signs and leave the end free: $D$2:D4. Copied down a column, the start stays on D2 while the end moves down a row each time. Here it covers three rows, 12, 7 and 20, and returns 39.

  5. Add along a row

    Can SUM add across a row instead of down a column?

    Yes, a range can run sideways too. D2:E2 adds the two numbers in the boxer cartridges' row, 12 and 3.5, to give 15.5. The same shape totals twelve month columns in a budget.

SUM or the plus sign?

Both add, but they treat text differently. The three test cells hold 10, 30 and '20, a 20 stored as text. SUM of the range skips the text and returns 40. Adding the same cells with + turns the text into a number and returns 60.

Use + for two or three single cells. Use SUM for ranges: it ignores a header or an empty cell, and one range is quicker to type and check than a long chain of plus signs.

Watch out for

Numbers stored as text are left out

A number typed with an apostrophe in front, or imported as text, is text to Excel. SUM skips it without any warning, so the total comes out short: 40 where you expected 60. Convert those cells to real numbers and the total is right.

  • =SUM(Data!AZ1:AZ3) 40
  • =Data!AZ1+Data!AZ2+Data!AZ3 60

One error in the range breaks the total

If any cell in the range holds an error such as #N/A, SUM returns that error instead of a total. Find the cell with the error and fix it at its source.

  • =SUM(Data!C1:C5) #N/A

Decimals can leave a tiny remainder

Excel stores numbers in binary, so 0.1 plus 0.2 is stored as 0.30000000000000004. The cell shows 0.3. Wrap the total in ROUND before you compare it with an exact amount.

  • =SUM(0.1,0.2) 0.3

Text typed into the formula is treated differently

Inside a range, text is skipped. Typed straight into the formula, it is converted if it can be: SUM("2",3) returns 5. Text that can't become a number, such as "a", gives #VALUE!.

  • =SUM("2",3) 5
  • =SUM("a",3) #VALUE!

The limit is 255 arguments

One SUM takes up to 255 arguments, and Excel refuses to enter a formula with a 256th. A range counts as one argument however many cells it covers, so you will rarely meet this.

  • =SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1) 255
  • =SUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1) Excel will not accept this formula

Questions people ask

How do you use the SUM function in Excel?

Type =SUM(, select the cells to add, close the bracket and press Enter. For the first six rows of the Qty column, D2:D7, that gives 68.

  • =SUM(Items!D2:D7) 68
How do I add up cells that aren't next to each other?

Separate them with commas. Ranges use a colon, single cells stand on their own, and one formula can mix both.

  • =SUM(Places!F2:F6,Places!F10) 11.25
How can I add up cells that contain specific text?

Use SUMIF with a wildcard. This one adds the quantities of every item whose name ends in pipe and returns 22.

  • =SUMIF(Items!B2:B7,"*pipe",Items!D2:D7) 22
How do I get an average from SUM?

Divide the total by COUNT of the same range. The six items hold 68 units in all, an average of about 11.33 each.

  • =SUM(Items!D2:D7)/COUNT(Items!D2:D7) 11.33333333

Where it works

Excel
SUM is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Tasks that use SUM

Part of Math and trig functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .