If your boss asks you for a report containing Sales by region, by Product, etc. you can do it using array formulas, pivot tables, SUMPRODUCT, filter, but the SUMIF function is the way you need to produce a usable report.
The syntax of the function is as follows: SUMIF(range, criterion, [sum_range])
Where:
Tidy: Is the array to compare with the criteria argument (for example, the array to search for Regions, Products, etc.). It can also contain numbers, 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 be Regions)
[sum_range]: Optional. It is the column that contains the figures to be added (Sales, Utilities, etc.)
Here are the advantages 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″).
- Sum all the lines in a column that match a text in another column, for example: =SUMIF(B2:B25,“Suzanne”,C2:C25).
- Adds all the lines in one column that match a date in another column, for example: =SUMIF(B2:B25,“7/11/2009”,C2:C25).
- Add all lines in one column that match a number in another column, for example: =SUMIF(B2:B25,10,C2:C5).
- It allows you to avoid Array Formulas and SUMPRODUCT if you haven’t mastered them yet.
- Allows you to use wildcards. For example: =SUMIF(A2:A10,” Wilson”,B2:B10) or =SUMIF(A2:A10,” “&E2,B2:B10).
- Provides flexibility to enter dates in the criteria argument. For example: =SUMIF(I9:I12,”7/11/2009″,J9:J12).
Here are the downsides of why you shouldn’t use the SUMIF function
- It does not evaluate multiple conditions.
- Criteria syntax is not that clear for input, eg how to input dates, how to input conditions. For example: “a”” or put the “a” (without the voice marks) in E2 and enter the criteria like this: “&E2, etc.
- It is not case sensitive.
- It can cause errors when the shape and size of sum_range and range are not compatible.
conclusion
The benefits of using the SUMIF formula outweigh its drawbacks, but you have to weigh each of the drawbacks, for example, the inability to evaluate multiple conditions makes me move in the direction of array formulas.
Array formulas are unavoidable; at least use Excel 2007, which removes the SUMIFSET function, but this formula is still not case sensitive.