Excel tips: how to summarise multiple data sheets into one report

Question

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.

Answer

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.

Syntax

SUMIFS(Sum_range,Criteria_range1,Criteria1,…)

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.  

Syntax

INDIRECT(Ref_Text)

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.

=SUMIFS(INDIRECT("'"&B$4&"'!"&B$1),INDIRECT ("'"&B$4&"'!"&B$2),$A5)

This formula can be copied down and across.

F9 function key

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.

Structural changes

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.

Volatility

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.

Closed files

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]