SUM function

This article describes the formula syntax and usage of the SUM function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The SUM function adds all the numbers that you specify as arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.). Each argument can be a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.), a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), a constant (constant: A value that is not calculated. For example, the number 210 and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.), a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. For another example, SUM(A1, A3, A5) adds the numbers that are contained in cells A1, A3, and A5.

Syntax

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

The SUM function syntax has the following arguments:

  • number1    Required. The first item that you want to add.
  • number2, number3, number4, ...    Optional. The remaining items that you want to add, up to a total of 255 items.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.

Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.


 
1
2
3
4
5
6
7

8


9

10


11




12
A B C
Data
-5
15
30
'5
TRUE
Formula Description Result
=SUM(3, 2) Adds 3 and 2. 5
=SUM("5", 15, TRUE) Adds 5, 15 and 1. The text value "5" is first translated into a number, and the logical value TRUE is first translated into the number 1. 21
=SUM(A2:A4) Adds the values in cells A2 through A4. 40
=SUM(A2:A4, 15) Adds the values in cells A2 through A4, and then adds 15 to that result. 55
=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 are not 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. 2

Remarks

  • If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
  • If any arguments are error values, or if any arguments are text that cannot be translated into numbers, Excel displays an error.
 
 
Applies to:
Excel 2007