|
<< Click to Display Table of Contents >> SUMIFS (sum if conditions are true) |
Syntax:
SUMIFS(SumRange, Range1, Criterion1 [, Range2, Criterion2 ...])
Description:
Returns the sum of those values in a cell range that fulfill all of the specified criteria.
This function is similar to the SUMIF function, but allows you to specify more than just one range/criterion to be searched.
Apart from that, the order of the parameters is different: In SUMIF, SumRange is the last parameter – in SUMIFS, it is the first.
SumRange is the cell range containing the values to be summed.
Range1 is the first cell range to be checked for the specified criterion. This range must have the same dimensions as SumRange.
Criterion1 is the condition that the values in Range1 have to fulfill to be included:
Use numbers or text (like "42" or "bolts") to obtain the sum of all cells that contain that value.
Use conditions (like ">10" or "<=5") to sum all cells that match the specified condition.
Note: Criteria always have to be surrounded by double quotation marks (").
Unlike the SUMIF function, SUMIFS allows you to specify more than just one cell range and criterion to be checked. If you do so, only occurrences where all criteria are fulfilled are included in the calculation of the sum.
Compatibility notes:
This function is not supported by the .xls file format (used in Microsoft Excel 2003 and earlier). If you save a document in this format, all calculations using this function will be replaced by their last result as a fixed value.
Accordingly, if you use this function, you should not save your document in the "Microsoft Excel 97-2003 (.xls)" file format, but choose one of the following formats instead:
▪"PlanMaker document (.pmdx or .pmd)"
▪or: "Microsoft Excel 2007-2021 (.xlsx)"
Example:
SUMIFS(A1:A10, C1:C10, ">1", C1:C10, "<2")
This formula returns the sum of only those cells in the cell range A1:A10 where the corresponding cell in the range C1:C10 is greater than 1 and less than 2.
See also: