Documentation/Calc Functions/SUM
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
SUM
Category:
Mathematical
Summary:
Calculates the sum of a set of real numbers by adding them.
Syntax:
SUM(Number 1 [; Number 2 [; … [; Number 255]]])
Returns:
Returns a real number that is the result of adding together the specified numbers.
Arguments:
Number 1, Number 2, … , Number 255 give the set of real numbers for which the sum is to be calculated. Each argument may take one of the following forms:
- A real number, or an expression that evaluates to a real number.
- A reference to a single cell containing a real number.
- A simple reference to a cell range containing real numbers (for example, A1:B9).
- The name of a named range, comprising cells containing real numbers.
- The name of a database range, comprising cells containing real numbers.
- An inline array of real numbers (for example, {1.1, 2.2, 3.3, 4.4}).
Note that although SUM can accept up to 255 arguments, each argument could specify a range of cells. This means that the number of real numbers processed could be many more than 255.
The following conditions (including errors) may be encountered:
- If any argument is a string in quotation marks, then SUM reports a #VALUE! error.
- Text in cells and empty cells are ignored.
- If there are no supplied numbers, then SUM returns the value 0.
- If only one value is supplied, then SUM returns that value.
Additional details:
- If N1, N2, …, Nk represent a set of k real numbers passed to the function, the formula for SUM is:
[math]\displaystyle{ \text{SUM}(N_{1}; N_{2}; ...; N_{k})=N_{1}+N_{2}+...+N_{k} }[/math]
- SUM ignores any text in cells or empty cells. If you suspect wrong results, look for text within the supplied data range(s). To highlight text content within a data range, use Calc's ▸ feature.
Examples:
Basic examples
| Formula | Description | Returns |
|---|---|---|
| =SUM(2; 3; 4) | Here the function calculates 2+3+4 and returns the result. | 9 |
| =SUM(A1:A3; B1:B2) where cells A1, A2, A3, B1, and B2 contain the numbers 2.2, 3, -5, 1.3, and -7 respectively. | Here the function calculates 2.2+3+(-5)+1.3+(-7). | −5.5 |
| =SUM({2; 4; 6; 8}) | Here the function calculates 2+4+6+8 and returns the result. | 20 |
Using SUM with conditions in an array formula
You have entered invoice data into a Calc sheet, and you want to use the SUM function to create a formula that will return the total value of all invoices dated within January 2008.
Column A contains the invoice dates (in ISO 8601 format) and column B contains the invoice values (in US dollars). The invoice dates are in the cell range A2:A20, and the corresponding invoice values are in the cell range B2:B20.
For convenience the date of the first day of the month of interest (2008-01-01) is entered in cell E2, and that of the last day in the same month (2008-02-01) is entered in cell E3.
The data is organized as in the following table.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice Date | Invoice Value | |||
| 2 | 2008-01-19 | $2,032 | Start Date | 2008-01-01 | |
| 3 | 2008-01-29 | $3,491 | End Date | 2008-01-31 | |
| 4 | 2008-02-20 | $1,660 | |||
| 5 | 2008-02-27 | $1,146 | Total Value | $10,889 | |
| 6 | 2008-02-27 | $1,790 | |||
| 7 | 2008-03-18 | $3,814 | |||
| 8 | 2008-04-04 | $4,045 | |||
| 9 | 2008-04-18 | $3,060 | |||
| 10 | 2008-04-29 | $1,316 | |||
| 11 | 2008-05-06 | $575 | |||
| 12 | 2008-06-02 | $2,213 | |||
| 13 | 2008-06-03 | $1,819 | |||
| 14 | 2008-01-25 | $3,116 | |||
| 15 | 2008-01-27 | $2,095 | |||
| 16 | 2008-01-28 | $155 | |||
| 17 | 2008-02-27 | $4,433 | |||
| 18 | 2008-04-02 | $1,355 | |||
| 19 | 2008-04-25 | $2,564 | |||
| 20 | 2008-05-12 | $3,887 |
To calculate the total value of the invoices in January 2008, the following formula is entered into cell E5 as an array formula.
=SUM((A2:A20>=E2)*(A2:A20<=E3)*B2:B20)
To enter this as an array formula, press the Shift + Ctrl + Enter keys instead of simply pressing the Enter key when you have typed the formula in the Formula bar. The formula will then be shown in the Formula bar enclosed in braces (the braces are automatically added and should not be typed when entering the formula).
{=SUM((A2:A20>=E2)*(A2:A20<=E3)*B2:B20)}
The single argument passed to SUM is then evaluated in array mode in advance of passing the result to SUM.
The formula uses the fact that the result of a comparison is 1 if the criterion is met and 0 if the criterion 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 ($10,889).
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
SUM