Documentation/Calc Functions/SUMIFS

Function name:
SUMIFS

Category:
Mathematical

Summary:
Calculates the sum of a set of numbers in a cell range, with the cells to be added together determined using multiple criteria. The primary difference between SUMIFS and Calc’s SUMIF function is that the latter is limited to evaluating only one criterion.

The criteria passed to SUMIFS can utilize wildcards or regular expressions.

Syntax:
SUMIFS(SumRange; Range 1; Criterion 1[; Range 2; Criterion 2[; … [; Range 127; Criterion 127]]])

Returns:
Returns a real number that is the result of adding together the numbers in relevant cells.

SumRange argument
SumRange specifies the cells to be summed. SumRange usually takes one of the following forms:


 * A reference to a cell range (for example, A1:A25), which may not utilize the reference concatenation operator (~).
 * The name of a named range.
 * The name of a database range.

Range arguments
Range 1 specifies the set of cells to be matched against Criterion 1 and takes one of the forms listed for SumRange. Range 1 should have the same dimensions as SumRange.

Range 2, ..., Range 127 have the same meaning as Range 1.

Criterion arguments
Criterion 1 is the criterion for matching against the cells in Range 1, or a cell containing that criterion. Criterion 1 can take one of the following forms:


 * A number, such as 34.5. Dates and logical values (TRUE or FALSE) are treated as numbers.
 * An expression, such as 2/3, SQRT($D$1), or DATE(2021; 11; 1).
 * A text string, such as "golf" or "<>10".

SUMIFS looks for cells in Range 1 that are equal to Criterion 1, unless Criterion 1 is a text string that starts with a comparator (>, <, >=, <=, =, or <>). In the latter case SUMIFS compares the cells in Range 1 with the remainder of the text string (interpreted as a number if possible and text otherwise). For example, the condition ">4.5" tests if the content of each cell is greater than the number 4.5, the condition "2021-11-01" tests if the content of each cell is not equal to the specified date.

Criterion 1 supports the following specific behaviors:
 * The string "=" matches empty cells. For example the formula  returns the sum of all values in the range A1:A10 if all cells in the range B1:B10 are empty. Note that "=0" does not match empty cells.
 * The string "<>" matches non-empty cells. For example the formula  returns the sum of all values in the range A1:A10 if there are no empty cells in the range C1:C10.
 * If the value after the <> comparator is not empty, then Criterion 1 matches any cell content except that value, including empty cells.

Criterion 2, ..., Criterion 127 have the same meaning as Criterion 1.

Error conditions

 * If any cell range passed as an argument contains a reference concatenation operator (~), then SUMIFS reports an invalid argument error (Err:502).


 * All the cell ranges passed as arguments (SumRange and Range 1, …, Range 127) must occupy the same number of rows and the same number of columns. If this is not the case, then SUMIFS reports an invalid argument error (Err:502).


 * If the Range n and Criterion n arguments are not correctly paired, then SUMIFS reports a variable missing error (Err:511).

Details specific to SUMIFS function

 * The default matching performed by SUMIFS is case-insensitive. However, a case-sensitive match can be carried out when using a regular expression by including a mode modifier "(?-i)" within the regular expression, as demonstrated by one of the examples below.


 * The behavior of SUMIFS is affected by several settings available on the dialog ( on macOS).


 * If the checkbox is ticked for Search criteria = and <> must apply to whole cells, then the condition "red" will match only "red"; if unticked it will match "red", "Fred", "red herring".
 * If the checkbox is ticked for Enable wildcards in formulas, the condition will match using wildcards - so for example "b?g" will match "bag", "beg", "big", "bog", and "bug".
 * If the checkbox is ticked for Enable regular expressions in formulas, the condition will match using regular expressions - so for example "r.d" will match "red", "rid", and "rod", while "red.*" will match "red", "redraw", and "redden".
 * The setting of the Case sensitive checkbox has no impact on the operation of SUMIFS.

Stationery sales examples
Consider the following table showing sales and revenue information for a small stationery supplier. The string "N/A" refers to products that were not available for supply during the period covered by the data.

In all examples based on this table, it should be noted that row 6 for pencil cases contains no numeric data and so will never contribute to the result of SUMIFS, whatever criteria are specified.

Sporting equipment sales examples
The examples in this subsection are based on a small database of sales data for sports equipment, with the data organized as in the following table.

Additional examples
For more examples, download and view this [[Media:Calc_Functions_ifs.ods|Calc spreadsheet]].

Related LibreOffice functions:
AVERAGEIFS

COUNTIFS

SUMIF

ODF standard:
Section 6.16.63, part 2

Equivalent Excel function:
SUMIFS