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_rangeThe 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_rangeThe 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.
criteriaThe 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

WildcardMeaningExample
*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

FormulaResultExplanation
=SUMIFS(B2:B9,A2:A9,”>6″,C2:C9,”Finance”)70000Sum the salary if Employee ID >6 AND Department = Finance
=SUMIFS(B2:B9,C2:C9,”<>IT”,C2:C9,”*T*”)80000Sum 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

 

 

Excel SUMIF Function

What is Excel SUMIF Function?

Excel SUMIF Function sum up an array (a range of values) if condition is met. Note that you can set only one condition, for multiple conditions you need the SUMIFS Function (with “s” in suffix)

Read the below article about SUMIFS Function

Excel SUMIFS Function

Syntax of Excel SUMIF Function

SUMIF( range, criteria, [sum_range] )
rangeThe 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.
criteriaThe criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.
sum_rangeOptional. 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).

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

WildcardMeaningExample
*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 of SUMIF Function

sumif_001

FormulaResultExplanation
=SUMIF(A2:A9,”>5″,B2:B9)130000Sum the salary if employee ID >5
=SUMIF(B2:B9,”>50000″)210000Sum the salary if salary > 50000
=SUMIF(C2:C9,”IT”,B2:B9)30000Sum the salary if Department = “IT”
=SUMIF(C2:C9,”Fin*”,B2:B9)130000Sum the salary if Department that starts with Fin
=SUMIF(C2:C9,”*~**”,B2:B9)80000Sum the salary if Department start * in the middle

Outbound References

http://office.microsoft.com/en-gb/excel-help/sumif-function-HP010062465.aspx