Excel SUMIFS Function

What is Excel SUMIFS Function?

Excel SUMIF Function sum up an array (a range of values) if condition is met. Excel SUMIFS function is an advanced version of SUMIF, allow you to set multiple conditions.

If you only need to apply one criteria, please read the use of SUMIF

Excel SUMIF Function

Syntax of Excel SUMIFS Function

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )

sum_range The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
criteria_range The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
criteria The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.

Remarks for criteria

Condition on Number

Note carefully that if you apply condition on number, you should double quote the criteria.

For example, if your criteria is >3, you should type “>3”

If your criteria on number is without a conditional operator (such as >), you can double quote the criteria or without double quote.

For example, if your criteria is 3, you can either type “3” or simply 3

To avoid confusion, I would recommend to use double quote under all circumstances.

Condition on Text

First of all, you should use double quote on all conditions on text

Second, you can use wildcard characters

Wildcard Meaning Example
* Represents one or more characters (any character) J*     any text that starts with J
*J     starts with any text but ends with J
*J*   any text that has J in the middle
? Represents one character (any character) J?     2 characters that start with J
?J     2 characters that end with J
?J?   3 characters with J in the middle
~ Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ? J~**   any text that starts with J*
~**J   any text that starts with * and ends with J
~?*~* any text that starts with ? and ends with *

Example

sumif_001

Formula Result Explanation
=SUMIFS(B2:B9,A2:A9,”>6″,C2:C9,”Finance”) 70000 Sum the salary if Employee ID >6 AND Department = Finance
=SUMIFS(B2:B9,C2:C9,”<>IT”,C2:C9,”*T*”) 80000 Sum the salary if Department <>IT AND Department contains T

Outbound References

https://support.office.com/en-US/Article/SUMIFS-function-9dd6179e-cced-41dd-ac38-08fdf5b929e5?ui=en-US&rs=en-US&ad=US

http://www.techonthenet.com/excel/formulas/sumifs.php

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *