|
<< Click to Display Table of Contents >> SUMIFS (sum if conditions are true) |
Syntax:
SUMIFS(SumRange, Range1, Criterion1 [, Range2, Criterion2 ...])
Description:
This 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 it allows you to specify multiple search ranges (including one criterion for each range).
Note: The order of the parameters is different here: In SUMIF, SumRange is the last parameter – while it is the first parameter in SUMIFS.
SumRange is the cell range that contains 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 "screws") to obtain the sum of all cells that contain this value.
Use conditions (like ">10" or "<=5") to sum all cells that match the specified condition.
Note: Criteria always have to be enclosed within double quotation marks (").
Unlike the SUMIF function, SUMIFS allows you to specify multiple search ranges (including one criterion for each range). 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 with their current result as a fixed value.
If you want to 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: