Summarising data from multiple, different data sheets is a big ask in Excel. In this case, it’s best to take the “indirect” route to get to your destination.
How can I summarise transactional data from multiple data sheets into one report? The complication is I am regularly adding new data sheets and the sheets are laid out differently.
This appears to be a difficult situation to solve, but Excel has a function that can turn chaos into structure. It is the INDIRECT function. If I can’t solve a problem with Excel’s other functions, then INDIRECT often saves the day.
The image in Figure 1 demonstrates the issue that we need to address. There are three separate sheets with three separate layouts that need to be summarised into a single report. The report needs to summarise the suppliers by each individual project.
The supplier name and the amounts are in different columns in the three different project sheets. These are transactional data sheets, which means the supplier name will be repeated throughout the data.
To summarise the total spend per supplier, we will use the SUMIFS function. In this simplified example, the sheets only have the two rows of data shown in Figure 1.
Sum_range – a range that holds the values to be added up. Usually a single column range, but can be a single row range.
Criteria_range# – a range that holds the items to be checked against the Criteria. Usually a single column range, but can be a single row range. If the Criteria matches the entry in the Criteria range, then the corresponding value from the Sum_range is added up.
Criteria# – the entry to be looked for in the Criteria_range. Usually a cell reference.
The SUMIFS function allows you to SUM based on multiple criteria. In our case, we only require one criteria to create our summarised report. The Sum_range and Criteria_ranges must line up for the function to work.
The Criteria_range and Criteria are paired together. You can have multiple criteria to determine if the Sum_range value is added up. These paired arguments are added to the right, within the SUMIFS brackets.
The SUMIFS function will need to refer to different columns for each of the project sheets. The challenge is to create a single formula that can be copied across and down to summarise these three sheets but also handle extra sheets. The INDIRECT function makes this reasonably easy.
Whole of organisation dashboard in excel - recorded webinar
Ref_Text – any text reference that can be converted into a cell reference; range reference or range name. The INDIRECT function converts the Ref_Text into a reference that Excel can use as a cell, range or range name reference. Typically, you use entries in other cells to build up the Ref_Text. This gives you the flexibility to handle data that is structured differently between sheets.
The layout for the solution is shown in Figure 2.
I have entered the amount column reference in row 1 and supplier name column reference in row 2. This technique relies on having the exact sheet name in row 4. If the sheet name is slightly different, the technique does not work.This technique also allows for the addition of extra sheets by simply filling in the references for the supply column, the amount column and the sheet name for the new sheet on the right of the existing report.
INDIRECT function tip
When you start to use INDIRECT, I recommend that you create the first formula in the normal way. Then review that formula and replace the parts of the formula that you need to be flexible with the INDIRECT function. In our case, cell B5 will summarise the Project A sheet. The SUMIFS formula we want to create is:
=SUMIFS('Project A'!C:C,'Project A'!B:B,$A5)
I have referred to the whole column to shorten the formula – this can have an impact on calculation speed. There are two important things to note about the references used in this formula:
- The apostrophes at either end of the sheet name. These only appear when a sheet name contains a space.
- The exclamation mark that precedes the column reference.
Both of these need to be included in any references that we build to other sheets.
Rows 1, 2 and 4 in Figure 2 contain the building blocks for all the references. Formulas within the INDIRECT brackets will replicate the two sheet references that the SUMIFS function requires. The two external sheet references will be replaced with two INDIRECT functions. The final B5 formula is shown below.
This formula can be copied down and across.
F9 function key
The F9 function key is the calculation shortcut key for Excel. You can select part of a formula within the Formula Bar and then press F9. The result of the selected part will be displayed. You must select a part of the formula than can be calculated on its own. I use this in my training and it is also useful when you are trying to debug formulas. See the result of pressing F9 in Figure 3.
The result is a combination of the sheet name, exclamation mark and column reference. The INDIRECT function converts this text reference into a real reference that the SUMIFS function can use.
Figure 4 shows the formula copied across and down, as well as a new project being added to the right of the report.
How to use trial balances to streamline data in Excel
In the example left, the column references and sheet names are all entered into cells. These will not update if the sheet name is changed or if a column is inserted into the sheets. In those cases, the cell inputs must be updated for the formulas to work.
The INDIRECT function is volatile, meaning that it re-calculates every time Excel calculates, whether it needs to or not. Most Excel functions only re-calculate if values in their references change. The more INDIRECT functions you have in a file, the slower it will calculate. This is less of an issue now with our fast PCs and laptops.
The SUMIFS and INDIRECT functions will not work with references to closed external files. The external files must be open for the functions to return results.
The companion video and an Excel file may assist your understanding.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]