Formulas and functions > Functions from A to Z

Syntax:

SUM(Number1 [, Number2, Number3 ...])

Description:

This returns the sum of the given numbers.

Number1, Number2, etc., are individual values, cell ranges or arrays containing the values to be summed.

If you use this function with cell references (for example A1:A3), only numbers are considered, while text strings and logical values occurring in the referenced cells are ignored.

If you use this function with fixed values that you typed in directly, numbers entered as a text string (for example, "42") are included. Furthermore, the logical value TRUE is treated as 1 and FALSE as 0. See examples below.

Example:

SUM(1, 2, 3, 4) equals 1+2+3+4, which is 10

If the cells A1:A4 contain the values 1, 2, 3 and 4:

SUM(A1:A4) equals 1+2+3+4, which is 10

Please note:

If the cells A1:A3 contain 1, "2" and TRUE:

SUM(A1:A3) returns 1, since the text string and the logical value are ignored.

They are counted only if they were typed in directly as fixed values:

SUM(1, "2", TRUE) returns 1+2+1, which is 4

Note:

There are some special terms for the SUM formula, where Calculate automatically completes the formula with additional functions. The following examples illustrate how it works:

If you enter SUM(A5:A), all values in column A are added, except A1:A4.

If you enter SUM(E2:2), all values in row 2 are added, except A2:D2.

If you enter SUM(A:A10), all values of A1:A10 are added.

If you enter SUM(2:E2), all values of A2:E2 are added.

Tip:

You can also use the AutoSum icon on the ribbon tab Home | group Contents to quickly calculate the sum of a specific cell range:

Select an empty cell directly below a group of cells containing numbers and then click the AutoSum icon. The Sum function for the cell range above is now automatically inserted into the selected cell. You just have to press the Enter key↵ to display the result.

Alternative method: Select a range of cells with numbers and then click on the AutoSum icon, which will insert the sum of this cell range directly below these cells.

You can also apply the AutoSum icon for several other arithmetic functions. To do so, click on the arrow to the right of the icon. Calculate will open a dropdown list containing the available functions – for example, Count, Product, Average, etc. Select the desired function from the list to apply it to a cell range using the methods just described.

Note: The last selected function from the list will be applied again when you directly click on the AutoSum icon.

SUM (Sum)

SUM (Sum)