SUMIFS … from hours to seconds

In June of this year I ran a course for a company, Financial Analysis, Modelling and Forecasting: I run this course several times a year and it is always well received.

As with all of my courses, I took questions and problems from the delegates and for one of them I ran out of time to finish during the week of the course. In the end I asked the delegate to wait a while for the answer and he agreed!

Now I have an answer: well, three answers to be fair. I set some other delegates on the latest running of this course the task of helping delegate 1: try to find the most efficient way of transforming raw data into a finished Cost Accounting Report, fully formatted and accurate.

Three solutions:
• a pivot table solution
• an INDEX and MATCH solution
• a SUMIFS solution
All solutions work and do what was asked. Moreover, you could use each solution as a template for future use. For confidentiality reasons I am not able to share the file and workings with this group, I am sorry to say.
The pivot table solution requires you to amend or add to your input in order to prepare the pivot table. You then use the pivot table as the source for your cost report by using this example formula:

=IFERROR(GETPIVOTDATA(“Sum of Amount in local cur.”,’Pivot Table’!\$A\$3,”Type”,\$A7,”Sub Type”,\$B7,”Material”,\$C7,”G/L”,G\$3),0)

The INDEX MATCH solution also requires you to amend you inputs a little but the solution works and it does not require you to establish new worksheets or a pivot table. A specimen formula for using the INDEX MATCH solution looks like this:

=IFERROR(INDEX(May_2014!\$A\$3:\$A\$269,MATCH(‘INDEX&MATCH’!B6&\$D\$3,May_2014!\$F\$3:\$F\$268,0)),””)

The optimal solution, however, is the SUMIFS solution. As you will see this is by far the most efficient, shortest and most direct method of all three. Remember, all three solutions work but this is the most elegant. You add nothing to the input table. You add no more worksheets or ranges. SUMIFS just takes a look at the inputs and picks and pastes what it needs. Two specimen formulas look like this:

For the quantity column =SUMIFS(May_2014!\$A:\$A,May_2014!\$D:\$D,SUMIFS!B5)

For the other columns =SUMIFS(May_2014!\$B:\$B,May_2014!\$D:\$D,SUMIFS!\$B5,May_2014!\$E:\$E,SUMIFS!D\$3)

Conclusion

If you have a later version of Excel and need to grab and paste data like this, VLOOKUP works, INDEX and MATCH work, Pivot Tables work and other approaches can work … take a look at SUMIFS too. I think delegate 1 will save around 2 – 3 hours a time by using SUMIFS as opposed to the methods I saw him using in June. The savings come from NOT having to reorganise the data, which he did; from not setting up functions and sub functions, which he did; from not setting up formulas every period, which he did.

Use the inputs and output table as a template and setting up the SUMIFS formulas is something you do just once! The beauty of a template!

Duncan Williamson

This site uses Akismet to reduce spam. Learn how your comment data is processed.