Shares

How to use Excel SUMIFS Function

Quick Intro

Use Excel SUMIFS Function to sum values which meet multiple criteria which you have specified. In simple words, you can specify more than one criteria to check and get SUM of values where criteria are matched.

Syntax

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

• [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. [Optional]
• [criteria2] A criteria which can be a number, text, expression, cell reference or a 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

In the below example, we have used three different criteria to check. And, I’ll get the sum for the values which meet those criteria.

In the below example, I have used Wildcard Characters to specify three different criteria. It will work in below pattern.

1. First criteria range is A2:A9 and criteria is in cell A12 which will only sum cells which have alphabet O.
2. 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.
3. 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 and 2.

What’s Next?

To learn more about Excel SUMIFS Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

There is also you have SUMIF function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.