When to use Excel SUMIFS Function

SUMIFS function can be used when you want to add the values in a range if multiple specified criteria are met.

What it Returns

It returns a number that represents the sum of all the numbers for which the specified criteria are met.


=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Input Arguments

range – the range of cells against which the criteria is evaluated. It could be numbers, text, arrays, or references that contain numbers. Blank and text values are ignored. criteria – the criteria that is checked against the range and determines which cells to add. sum_range – (optional) the cells to add. If this argument is omitted, it uses range as the sum_range. [criteria_range2] – (optional) the range of cells against which the criteria2 is evaluated. criteria2 – the criteria that is checked against the criteria_range2 and determines which cells to add.

Additional Notes

Adds values based on multiple criteria. Blanks and/or text in sum_range are ignored. Criteria could be a number, expression, cell reference, text, or a formula. Criteria which are text or mathematical/logical symbols (such as =,+,-,/,*) should be in double quotes. Wildcard characters can be used in criteria. Criteria should not be longer than 255 characters. Cells in sum_range are added only when all the conditions are met. Cells in the sum_range argument that contain TRUE evaluate to 1; cells in sum_range that contain FALSE evaluate to 0 (zero). Size of sum_range and all the criteria_range must be equal.

Excel SUMIFS Function – Live Example

Excel SUMIFS Function – Video Tutorial

Related Excel Functions:

Excel AVERAGE Function. Excel AVERAGEIF Function. Excel AVERAGEIFS Function. Excel SUM Function. Excel SUMIF Function. Excel SUMPRODUCT Function.

Related Excel Tutorials:

How to SUM values between two dates (using SUMIFS formula) Calculate the Number of Months Between Two Dates in Excel How to Sum a Column in Excel How to Sum Positive or Negative Numbers in Excel

Thank you