SUM function

The SUM function, one of the math and trig functions, adds values.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

This video is part of a training course called Add numbers in Excel 2013.

Note   After selecting and entering the complete formula (beginning with the equals sign =), press the Tab key to execute the formula. If you don't press the Tab key, the cursor will not move to the next cell and the formula will continue being edited, with unpredictable results. If unexpected values or selections appear in your completed formula, press the ESC key.

Syntax

The SUM function's syntax is the following: SUM(number1,[number2],...)

You can include up to 255 numeric values or cell or range references, in any combination, as arguments in the SUM function. For example:

  • =SUM(A2,A3)

  • =SUM(A5,A6, 2)

Argument name

Description

number1    (Required)

The first number you want to add. The number can be like 4, a cell reference like B6, a range of cells range like B2:B8, or a named range of cells.

number2    (Optional)

This is the second number you want to add.

You can specify up to 255 additional numbers in this way. Each argument after the first one is separated by commas.

Examples

To use these examples in Excel, select the data in the following table and press Ctrl+C. In Excel, click cell A1 in a new worksheet and press Ctrl+V.

Note   To recalculate any formula, select the cell that it's in, press F2, and then press the Enter key. To recalculate the entire worksheet, press F9.

Data

-5

15

30

'5

TRUE

Formula

Description

=SUM(A2,A3)

Adds the value in cells A2 and A3. The result is 10.

=SUM(A2:A4,15)

Adds the values in cells A2 through A4 first, and then adds 15. The result is 55.

=SUM("5", 15, TRUE)

Adds 5, 15 and 1. The text value "5" is translated into a number, and the logical value TRUE is first translated into the number 1. The result is 21.

=SUM(A5,A6,2)

Adds the values in cells A5 and A6, and then adds 2 to that result. Because non-numeric values in references aren't translated—the value in cell A5 ('5) and the value in cell A6 (TRUE) are both treated as text—the values in those cells are ignored. The result is 2.

=SUM(A2:A5)

Adds all the cells in the specified range of cells. A negative value in cell A2 is part of the range (-5), which is the same as subtracting that value. Because the value in cell A5 ('5) is treated as text, the result of this formula is 40.

A cell containing a value similar to '5 is automatically stored as text and is flagged with a green triangle appearing in the top-left corner of the cell:

Cell with a formula error
Cell with a formula error

Text characters in a cell such as apostrophes and alphabetic characters will cause the SUM function to ignore cells containing that data. For more information about using ranges with the SUM command, see Use the SUM function to sum numbers in a range.

Additional examples:

=SUM(my_values)

Adds all the cells that belong to the named range my_values. Named ranges are a great convenience feature. You can refer to a group of cells as an argument for formulas, collected under a single name for quick reference. You can create a named range by selecting cells A2 through A5 (click the mouse in the first cell and drag it across all the other cells for your selection), click Formulas > Define Name, type in the name for the range, and press Enter. You can name ranges whatever you want, using underscores instead of spacebar characters. For more information, see Overview of formulas in Excel.

For example, summing a named range called my_values, containing cells A2 to A5, provides 40 as the result because the negative value in cell A2 subtracts 5 from the result, while the value in cell A5 is treated as text and ignored by the formula.

When you create a named range, you'll notice a series of odd values that appear in the dialog box:

Named ranges dialog
Ranges are automatically created with Absolute values ($)

Excel automatically uses dollar signs ($) to create what are called absolute values to define named ranges. When you create a formula that refers to another cell or range, that cell reference can be relative or absolute. A relative cell reference adjusts to its new location when the formula is copied or moved. An absolute cell reference does not change if you move the formula. Excel automatically creates your new named range using absolute values. For more information, see Overview of formulas in Excel.

=SUM(my_values,2)

Adds all the cells that belong to the named range my_values and then adds 2 to the result. The result is 42.

=SUM(Sheet2!A2:A257,A20)

Refers to a range of cells in a different worksheet, adds all the cells from there, and then adds the result to the value from cell A20 in the current worksheet. For more information, see Overview of formulas in Excel.

=SUM(Sheet2!weekly_sales,A20)

Refers to a named range of cells called weekly_sales in a different worksheet, adds all the values from there, and then adds the result to the value from cell A20 in the current worksheet.

Notes   Excel does not provide a SUBTRACT function. To subtract values by using a function, you can use negative numeric values with the SUM function. For example, the formula =SUM(30,A3,-15,-B6) adds 30 and the value in cell A3, subtracts 15, and also subtracts the value in cell B6.

Another example: the formula =SUM(A4-A3) subtracts the value of cell A4 from cell A3.

Common Problems

Problem

What went wrong

Some numbers aren't added.

If an argument is a cell range or reference, only numeric values in the reference or range can be added. Empty cells, logical values like TRUE, or text strings are ignored.

The #NAME? error value appears instead of the expected result.

This usually means that the formula is misspelled.

I have a named range in my worksheet but I can't find it.

Click Formulas > Name Manager. The dialog box lists all the named ranges that are created in your worksheet.

I am seeing a ##### error in my formula's result.

The cell containing the formula and its results is not wide enough to contain the data. While the formula itself is not affected by cell width, the results for the sum calculation may be affected. To fix this, double-click near the right edge of the column header. See Correct a ##### error for more information.

I am seeing a #VALUE! error in my formula's result.

The SUM function uses only numeric arguments, cell references and cell ranges, or named ranges. Attempting to use a text string as an argument will produce a #VALUE! error.

I am receiving results equal to 0 in my SUM formula.

Ensure that you are referring to cells that are formatted as numbers; if you use a SUM formula to refer to cells that are formatted as text, the end result of the SUM will be zero. (To fix this, select all the cells in question, right-click, select Format Cells from the shortcut menu, and then click the Number tab. Summing a range of cells is unaffected by blank or zero values within the range; SUM ignores the zeroes or blanks. If you nest a multiplication function such as PRODUCT within a SUM function, and the multiplication has a cell with a zero as part of its calculation, you may also have unpredictable results.

I can't sum for a set of dates using my SUM formula!

The SUM function does not directly support the summing data associated with date values. To work with date ranges as part of a SUM formula, use the SUMIF or SUMIFS functions.

Related

  • Use the PRODUCT function for basic multiplication calculations.

  • Use the ROUND function to reduce the number of digits for a SUM result, or format the cell as Number type with the number of decimal places you need for your result. Select the cell, right-click, select Format Cells from the shortcut menu, and then click the Number tab.

  • Use the Autosum feature to quickly add up an entire column or row of data.

  • The SUMIF function adds only the values that meet a single criteria.

  • The SUMSQ function sums the values after it performs a mathematical square operation on each of them.

  • See a video on how to use the SUM function.

Applies To: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel Starter, Excel for Android phones



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Change language