Documentation/Calc Functions/SUM

From The Document Foundation Wiki
Jump to: navigation, search

Other languages:

Function name:

SUM

Category:

Mathematical

Summary:

Adds a set of numbers.

Im-jabber.svg

Note:
SUM ignores any text or empty cell within a range or array. If you suspect wrong results of the SUM function, look for text in the data ranges. Use the value highlighting feature to highlight the text contents in the range that may represent a number.

Syntax:

SUM(Number 1 [; Number 2 [; … [; Number 255]]])

Returns:

Returns a real number which is the sum of all the input numbers.

Arguments:

Number 1, Number 2, … , Number 255 are real numbers, references to cells or to cell ranges of those numbers, whose sum is to be calculated.

Additional details:

SUM ignores any text or empty cell within a range or array. If you suspect wrong results of the SUM function, look for text in the data ranges. Use the View > Value Highlighting feature to highlight the text contents in the range that may represent a number.

Examples:

Formula Description Returns
=SUM(2;3;4) sums up 2,3,4 9
=SUM(A1;A3;B5) where A1,A3,B5 have 2,3,4, respectively calculates the sum of the three cells 9
=SUM (A1:A5) where A!to A5 cell contains 2,3,4,5,6 calculates the sum of all cells in the A1 to A5 cell range 20
=SUM (A1:A5) where A!to A5 cell contains 2,3,4,5,6 calculates the sum of all cells in the A1 to A5 cell range 20

Conditions linked by AND can be used with the function SUM() in the following manner:

Example assumption: You have entered invoices into a table. Column A contains the date value of the invoice, column B the amounts. You want to find a formula that you can use to return the total of all amounts only for a specific month, e.g. only the amount for the period >=2008-01-01 to <2008-02-01. The range with the date values covers A1:A40, the range containing the amounts to be totaled is B1:B40. C1 contains the start date, 2008-01-01, of the invoices to be included and C2 the date, 2008-02-01, that is no longer included.

Enter the following formula as an array formula:

=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)

In order to enter this as an array formula, you must press the Shift+ Ctrl+ Enter keys instead of simply pressing the Enter key to close the formula. The formula will then be shown in the Formula bar enclosed in braces.

{=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)}

The formula is based on the fact that the result of a comparison is 1 if the criterion is met and 0 if it is not met. The individual comparison results will be treated as an array and used in matrix multiplication, and at the end the individual values will be totaled to give the result matrix.

Related LibreOffice functions:

AVERAGE

SUMIF

SUMIFS

ODF standard:

Section 6.16.61, part 2

Equivalent Excel function:

SUM