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
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
| 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
http://www.techonthenet.com/excel/formulas/sumifs.php