You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Liam Bastick has provided financial modelling services and training to clients for more than two decades. A senior accountant and professional mathematician, he has worked in numerous countries with many internationally recognized clients, providing and reviewing strategic and operational models for various key business assignments. You can check out Liam's previous articles at www.sumproduct.com/thought, where you can also subscribe to the monthly tips and tricks newsletter.
Ever had to sum data based on multiple criteria situated in different Microsoft Excel worksheets? This article provides a quick tour of INDIRECT references and Table functionality while combining qualities of the SUMPRODUCT function with the SUMIFS function, providing a solution to the mother-of-all Multiple Criteria problems.
The functionality is best explained by walking through an example:
Ivana Car Sales has four divisions, cunningly called North, South, East and West. Each quarter, the four divisions are required to submit sales reports detailing the month of sale, the sales person, the car color and the price the car was sold for.
The question is: how can you determine how many red cars Charlie sold in February in total across all four divisions?
The answer would be fairly straightforward if the data were all on one worksheet. For a single criterion, SUMIF would cope admirably well, while for several criteria, SUMPRODUCT could be used to generate the answer (for further information see my blog posts on the SUMPRODUCT function and approaches to addressing multiple criteria in one worksheet).
There are a few considerations for how the reports are set up:
If we are to refer to multiple datasheets, we need to know the names of these worksheets. For maximum flexibility, I would suggest storing these in a Table (highlight the data, and then, on the Insert tab of the Ribbon, select Table). Please see my blog for a full discussion on Tables.
I have named this Table Division_Table as this lists the divisions relevant for the analysis. To show why I have used a Table, note that I have not included the West division. If I were to type this into the row beneath East, West would become part of the Table. This is a very useful feature for referencing lists.
It is also important to note that the three divisions named (North, South and East here) must have identical names to the sheet tab names - otherwise, this solution will not work. Ensure that the text is precisely the same as that in the sheet tab.
We are now ready. In a separate worksheet, I would create the following table:
The formula in cell I12 here is probably one of the simplest you have ever come across (that is, if you happen to work in the world of Relativistic Quantum Mechanics!):
=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&Division_Table[Relevant Divisions]&"'!I:I"),INDIRECT("'"&Division_Table[Relevant Divisions]&"'!F:F"),$F12,INDIRECT("'"&Division_Table[Relevant Divisions]&"'!G:G"),$G12,INDIRECT("'"&Division_Table[Relevant Divisions]&"'!H:H"),$H12)),)
You know you have created a monster when you nest three complex Excel functions inside a fourth. But have no fear. To work out what is going on, I will explain from the inside out (as this is the order in which Excel will calculate this formula):
That's it. Now you'll be able to sum the data across the workbooks and report that Charlie's red car sales totalled $43k in February across the four divisions.
There are two other possible solutions to consider: PivotTables using data from multiple worksheets or creating a master data sheet as an interim step, where all data is recorded on one worksheet. I have produced this answer as this was faithful to the specific circumstances of the problem.
Final thought is: Keep it simple (also known as the KISS rule). Having data on multiple worksheets may complicate the problem and add complexity. Before writing multifaceted formulae like the ones discussed above, always consider simplifying the model structure first.
Masterful idea. One option, with a slight modification, you can build structured references instead. In this approach, name the tables instead of the sheets. This approach has multiple benefits:
1. Name the sheets whatever you like
2. Table columns can be in any order, even different orders on different sheets
3. Table can include different columns in the data set as long as the columns you need are in each table.
4. Tables can be anywhere in the workbook, even multiple tables on the same worksheet.
5. You can reference just the data instead of the whole column
=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT(Division_Table[Relevant Divisions]&"[Amount]"),INDIRECT(Division_Table[Relevant Divisions]&"[" & F11 & "]"),$F12,INDIRECT(Division_Table[Relevant Divisions]&"[" & G11 & "]"),$G12,INDIRECT(Division_Table[Relevant Divisions]&"[" & H11 & "]"),$H12)),)
Good work on these formulas. Just wanted to let you know (since the Excel Team apparently did not share this with you) that PowerPivot was created to provide an easier way to get this type of information from multiple tables.
I completely agree about PowerPivot being a simpler tool for solving this sort of problem. The problem is that this is only available in Excel 2010 and Office 2013 Professional Plus and Office 365 Pro Plus. It's not readily accessible to all, unfortunately - see www.cimaglobal.com/.../PowerPivot-2013-getting-the-right-version-of-Office-for-you ,