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
Syntax of Excel SUMIF Function
SUMIF( range, criteria, [sum_range] )
| 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. |
| sum_range | Optional. 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
| 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 of SUMIF Function
| Formula | Result | Explanation |
| =SUMIF(A2:A9,”>5″,B2:B9) | 130000 | Sum the salary if employee ID >5 |
| =SUMIF(B2:B9,”>50000″) | 210000 | Sum the salary if salary > 50000 |
| =SUMIF(C2:C9,”IT”,B2:B9) | 30000 | Sum the salary if Department = “IT” |
| =SUMIF(C2:C9,”Fin*”,B2:B9) | 130000 | Sum the salary if Department that starts with Fin |
| =SUMIF(C2:C9,”*~**”,B2:B9) | 80000 | Sum the salary if Department start * in the middle |
Outbound References
http://office.microsoft.com/en-gb/excel-help/sumif-function-HP010062465.aspx