SUMIFS

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	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.