Introduction to SUMIFS Function
SUMIFS Function returns the sum of the numbers which meet multiple conditions you specify. Unlike SUMIF, you can set multiple conditions and can sum only those values which meet all those conditions.
Download Sample file
Download Sample file for working with the tutorial.
Syntax of SUMIFS Function
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Arguments in SUMIFS Function
- [sum_range]: A range of cells & values which you want to sum.
- criteria_range1: A range from which you want to test criteria.
- criteria1: A criteria which can be a number, text, expression, cell reference or a function.
- [criteria_range2]: A range from which you want to test criteria.
- [criteria2]: A criteria which can be a number, text, expression, cell reference or a function.
Notes
- The size of the criteria range and sum range should be of the same size.
- You can also use wildcard characters.
- If you want to sum values based on single criteria, you can use SUMIF.
- 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 the criteria range that meet the criteria.
Tutorial of SUMIFS Function in MS Excel
In the below example, we have used three different criteria to check and it has returned the sum of the values which meet those criteria.
In the below example, we have used wildcard characters to specify three different criteria.
- The first criteria range is A2:A9 and criteria in the cell A12 which will only sum cells that have alphabet O.
- The second criterion range is B2:B9 and criteria in cell B12 which will only sum cells that have alphabet P. It will apply to all the cells which met condition 1.
- The third criterion range is C2:C9 and criterion in cell C12 which will sum cells with any type of value. It will apply to all the cells which met the condition 1 and 2.
Other Math Function in Microsoft Excel
Explore other math function in Microsoft excel. The list of all the math function available for everyday use is given below –
