Skip to main content
Microsoft 365
Subscribe
4 min read

Summing data across multiple criteria on multiple worksheets

By

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?

Excel icon

Microsoft Excel

Turn data into insights.

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).

Setting up the reports

There are a few considerations for how the reports are set up:

  1. It is important that these reports are constructed from a master template, i.e., each report has the various report fields in the same corresponding column. This is important for what follows. If this is not the case, you should modify the datasheets accordingly.
  2. If the four reports are not stored in the same workbook, the data should be imported onto separate worksheets within the workbook where the output report is to be generated.

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.

How to: Multiple criteria, multiple worksheets

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):

  • INDIRECT (discussed in detail here)–This function produces an array of references such as ‘North’ column F, ‘South’ column F, etc. which can be used by the other functions. Note carefully that “‘” in the formula is inverted commas, an apostrophe (the syntax required in general for sheet names), then inverted commas.
  • SUMIFS— This function now applies the multiple criteria to the summation analysis. However, when used on its own, this will only report on the first worksheet created (the order of presentation within the workbook is irrelevant). That’s where the next function comes in…
  • SUMPRODUCT–This function is necessary as it is often referred to as a “pseudo array function”. What this means in practice here is that it will allow the SUMIFS function to be performed across all three worksheets.
  • IFERROR–This error trap ensures that if a worksheet listed in the Division_Table does not exist and/or there is a blank row, the formula will not produce an error – #REF!, for example.

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.

Alternative approaches

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.

—Liam Bastick