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] )
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

sumif_001

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

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

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 *