Excel® is a robust computer program for multiple tasks, from creating personal budgets and supplemental accounting documents to troubleshooting accounting software and even programming some software. With all that it offers, many of us still only use a fraction of its features. This series — Excel Pro Tips — covers some of the best formulas, tables, conditional formatting, and other processes that benefit users. Today’s article looks at the use of sumif for quick category totals.

Background

Say you have a feed log built in Excel. In the spreadsheet, you keep track of the pounds of feed given to each of the three livestock operations you have: cattle, hogs, and poultry. You do this so at the end of each month you can make some cost allocations for the feed to each of the livestock groups. Looking back you are kicking yourself that you made one feed log for the farm instead of three, one for each livestock group. Now, it is difficult to sum the numbers by livestock group. You wish there was a formula that could add up usage of each type of feed by animal group. That way you could have a quick summary of usage given your current data setup. Well, today we will look at a formula that does just that.  It is called sumif.

Sumif

Consider the following example in Excel.

To demonstrate the formula I made the usage easy to follow. Cattle will use 1 lb, hogs will use 2 lbs, and poultry will use 3 lbs of both silage and corn on each ration mixing date. In the box to the right, I have made the table in which I want to summarize the month's feed usage. So in J2, I want to make a formula that will total Cattle’s use of silage for the month. This is where the sumif formula comes in. By clicking in cell J2 and typing =sumif(, Excel will start guiding you.

The first section needs to identify the range in which the deciding factor is made to add the number or not. In our case, that range is B3:B9. Then we need to add a comma and add the next part of the formula that identifies the actual deciding factor. We want to pull just numbers that tie to cattle so type in “Cattle” with the quotation marks. Finally, type another comma and identify the range in which the numbers that need to be summed are located. For our example that range is C3:C9. We need to add a closing parenthesis to end the formula and then enter to exit the cell. Your end formula will look like this:

=sumif(B3:B9,”Cattle”,C3:C9)

The formula went through all nine entries, pulled out just the entries that were for cattle, and then added the silage number associated with that date. Cell referencing the word Cattle in J1 would be an improvement in our example as cell referencing should be used whenever possible. There may be some instances where you have more than one criteria you want to decide on. Excel also has that covered with sumifs. I could make a formula that only adds instances of silage fed to cattle after January 3, 2018.

Give it a try and let me know if you have questions or recommendations for future walkthroughs.