Documentation/Calc Functions/SUMIF
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
SUMIF
Category:
Mathematical
Summary:
Adds the cells specified by a given criteria. This function is used to browse a range when you search for a certain value.
Syntax:
SUMIF(Range; Criteria [; SumRange])
Returns:
Returns a real number which is the sum of cells in the range Range that qualify the given Criteria.
Arguments:
Range is the range to which the criteria are to be applied.
Criteria is the cell in which the search criterion is shown, or the search criterion itself. If the criteria are written into the formula, it has to be surrounded by double quotes.
SumRange is the range from which values are summed. If this parameter has not been indicated, the values found in the Range are summed.
SUMIF supports the reference concatenation operator (~) only in the Criteria parameter, and only if the optional SumRange parameter is not given.
Additional details:
- The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example, to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in Tools > Options > LibreOffice Calc > Calculate.
- See COUNTIF() for some more syntax examples that can be used with SUMIF().
Examples:
Formula | Description | Returns |
---|---|---|
=SUMIF(A1:A4;"<0") where A1,A2,A3,A4 are 3,-4,-5,-6 | sum up only negative numbers | -15 |
=SUMIF(A1:A3;">0";B1:B3) where A1,A2,A3 are 3,-4,5 and B1,B2,B3 are 2,3,4 | sums values from the range B1:B3 only if the corresponding values in the range A1:A3 are greater than 0 so here 3,5 are >0 hence 2+4 is returned | 6 |
Related LibreOffice functions:
ODF standard:
Equivalent Excel function:
SUMIF