Monday, November 14, 2011

How to use the SUMIF formula in Excel

SUMIF is a useful formula to know how to use when creating Microsoft Excel spreadsheet templates. An example I can give is from the world of engineering. Say you have to create a part list or a bill of material (BOM for an assembly, such as a snow blower. The snow blower has multiple parts with have standard parts, such as washers, bolts, nuts, etc,, attached to it. At the bottom of my BOM I want to sum all of the parts for my top level assembly but I don’t want to include the number of standard nuts and bolts. In this case I use the SUMIF formula along with some nifty formatting.

 
 
To count the number of parts in a specified column use the =SUMIF() formula. In my case, =SUMIF(K5:K17,">0").
If a number exists in a column which is greater than zero then sum the given range in the column.

One thing that I did was to change the standard part counts, which used to be entered as (1), (2), (3), etc. (entered '(1)). I have since changed this to a new format: -1, -2, -3... yet the entries still look like (1), (2), (3).  This way the SUMIF formula can count the numbers since they are not being recorded as text.  With the numbers being negative it can also make it so the formulas can selectively count them in the different parts of the spreadsheet.  So, when entering parts like weld nuts, nut plates, pin, collars, etc. enter the count as a negative (-) number.  This can either be done as -1 or (1), both will mean the same. I also put in a custom number format so you  no longer have  to type "-1", "-2", etc.  Just enter 1, 2, 3, etc. and the "-" will be placed in front automatically. Little improvements like this go a long way in improving the time it takes to compile one of these massive spreadsheets.

Let me know if you have any questions or concerns and if this format is more or less helpful and an improvement in function and readability.