SUMIF – Review of This Excel Function
If your boss asks you for a report that contains Sales by region, by Product, etc. you can do it using Array Formulas, Pivot Tables, SUMPRODUCT, Filter but the SUMIF function is the way when you need to produce a usable report.
The function syntax is the following: SUMIF(range, criteria, [sum_range])
Where:
Range: It is the array to compare against the criteria argument (for instance, the array to search the Regions, Products, etc). It can contains numbers too, for example Sales. In this case the criteria would be numeric and the optional sum_range argument is not used
Criteria: The condition to evaluate in the range array (for example, North, South, etc if the range would the Regions)
[sum_range]: Optional. It is the column that contains the figures to sum (Sales, Profits, etc)
Here are the pros of why you should use the SUMIF function
- It is easier to learn and write than Array Formulas.
- Allows you to summarize multiple lines into one.
- Allows you to use logical operators, for example: =SUMIF(B2:B25,”>5″).
- Sums all the lines in a column that match a text in other column, for example: =SUMIF(B2:B25,“Susan”,C2:C25).
- Sums all the lines in a column that match a date in other column, for example: =SUMIF(B2:B25,“11/7/2009”,C2:C25).
- Sums all the lines in a column that match a number in other column, for example: =SUMIF(B2:B25,10,C2:C5).
- Allows you to avoid Array Formulas and SUMPRODUCT if you are not proficient with them yet.
- Allows you to use wild cards. For example: =SUMIF(A2:A10,” Wilson”,B2:B10) or =SUMIF(A2:A10,” “&E2,B2:B10).
- Offers flexibility to enter dates in the criteria argument. For example: =SUMIF(I9:I12,”11/7/2009″,J9:J12).
Here are the cons of why you should not use the SUMIF function
- Does not evaluate multiple conditions.
- The criteria syntax is not so clear to enter, for example how to enter dates, how to enter conditions. For example: “a”” or place the “a” (without the speech marks) in E2 and enter the criteria this way: “&E2, etc.
- It is not case sensitive.
- It can causes errors when the shape and size of the sum_range and range are not compatible.
Conclusion
The benefits of using the SUMIF formula are more than its cons but you must evaluate the weight of each of the cons, for example the incapacity to evaluate multiple conditions make me act in the Array Formulas direction.
Array Formulas are inevitable; at least you use Excel 2007 – that disposes of the SUMIFS function, but this formula is still not case sensitive.
