The SUMIFS function adds range values that meet all specified criteria.
This can be useful when several conditions need to be taken into account when analyzing data.
Syntax
SUMIFS(sum_range, criterion_range1, criterion1, [criterion_range2, criterion2, ...])
Argument |
Description |
Permitted values |
|---|---|---|
sum_range |
Summation range |
Cell range |
criterion_range1 |
Cells whose values are checked for compliance with the first criterion. Suitable values are then selected within the sum range |
Cell range |
criterion1 |
Condition against which the value of the “criterion_range1” argument is checked |
Number, text, cell reference, or expression: •Equal to: “text”, 1, “=text”, or “=1” •Greater than: “>1” •Greater than or equal to: “>=1” •Less than: “<1” •Less than or equal to: “<=1” •Not equal to: “<>1” or “<>text” •Text with wildcards “?” and “*” |
[criterion_range2, criterion2, ...] |
(optional) Additional ranges and criteria for them |
Similarly, ranges and criteria |
Examples of use
Summing with multiple criteria in a single column
If you have sales data in the range B1:B10 and product categories in the range A1:A10, and you want to sum the sales for the “Fruits” category, taking into account only those sales that are greater than 100:
=SUMIFS(B1:B10, A1:A10, "Fruits", B1:B10, ">100")
Sums the values from the sales column (B) only if the category in column A “Fruits” and the corresponding sales value in the same column B exceeds 100.
Summing using cell references
If cell D1 specifies the category and D2 specifies the minimum amount, and you want to sum the sales that meet these criteria:
=SUMIFS(B1:B10, A1:A10, D1, B1:B10, ">" & D2)
This expression sums the sales where the category is equal to D1 and the amount is greater than D2.
Summing by multiple criteria with text and numbers
If you have sales data for different regions and want to sum sales for the “West” region where sales are less than 500:
=SUMIFS(B1:B10, A1:A10, "West", B1:B10, "<500")
Result: the sum of sales for the “West” region that are less than 500.
Summing with multiple ranges and conditions
You can use different ranges for different criteria:
=SUMIFS(B1:B10, A1:A10, "Vegetables", C1:C10, ">50")
Sums the values in B1:B10 for the “Vegetables” category if the values in cells C1:C10 are greater than 50.
Notes
–All ranges must have the same size, otherwise the function will return the #VALUE! error.
–The SUMIFS function allows you to use up to 127 pairs of ranges and criteria.