What is the use of Excel SUMIFS Function?
Use Excel SUMIFS Function to sum values which meet multiple criteria which you specified. In simple words, you can specify more than one criteria to check & get SUM of values where criteria are matched.
Syntax for Excel SUMIFS Function
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
sum_range A range of cells & values which you want to sum. [Optional]
criteria_range1 A range from which you want to test criteria. [Required]
criteria1 A criteria which can be a number, text, expression, cell reference or a function. [Required]
criteria_range2 A range from which you want to test criteria. [Optional]
criteria2 A criteria which can be a number, text, expression, cell reference or a function. [Optional]
More Information on Excel SUMIFS Function
- The size of criteria range & SUM range should have the same size of the range.
- You can also use Wildcard Characters in the sumif function.
- If you want to sum values on the basis of single criteria, you can use SUMIF Function.
- If you want to specify criteria in the form of text, you have to use double quotation marks.
- If you skip specifying SUM range it will SUM the values from criteria range which meet the criteria.
- You can only specify criteria up to 255 characters.
Examples to understand Excel SUMIFS Function
In above example, I have used three different criteria to check. And, I’ll get the sum for the values which meet those criteria.
In above example, I have used Wildcard Characters to specify three different criteria. It will work in below pattern.
- First criteria range is A2:A9 & criteria is in cell A12 which will only sum cells which have alphabet O.
- Second criteria range is B2:B9 & criteria is in cell B12 which will only sum cells which have alphabet P. It will apply to all the cells which met the condition 1.
- Second criteria range is C2:C9 & criteria is in cell C12 which will sum cells with any type of value. It will apply to all the cells which met the condition 1 & 2.
Download Sample File
Use Excel SUMIFS Function to SUM values which meet specified conditions. You can specify more than one condition. Learn more about SUMIFS from here.