No, no, no, no, no I’m not talking about the latest 3D animated movie.
First the data:
I’ve got 12 sheets just like the one below, one for every month – see the tabs at the bottom.
And I’ve got my summary sheet that totals up the data for each builder by region:
Here’s the problem (read ‘fun challenge’)
1. The data for each month contains multiple entries for some builders, so I can't simply sum it.
2. You can’t use the SUMIFS function across multiple sheets…well not on its own.
Solution 1: The slow option
If you’ve got oodles of time and a super computer you could add one SUMIF to another in one massive long formula like this:
Solution 2: The fast option
You can use a formula like this (from cell B5 of the summary sheet):
Note: The workbook download is a .xlsx file. If your browser changes the file extension to a .zip or .xml then you will need to replace it with .xlsx before saving the file.
How this formula works
If you know how to use SUMIF then you will recognise that cell A5 contains your criteria, in this case the Builder’s name.
The other thing to note is the reference ‘tabs’. This is the named range given to the list of my sheet tab names located in cells G2:G13:
Note: you don’t need to give your list of sheet tabs a named range. You could simply reference the cells like this:
Back to why we need to create a list of sheet tabs…listing out the sheet tab names enables you to use the INDIRECT function to create the reference to each sheet on the fly, which results in a short elegant formula.
The Bad News
Because sometimes we have workbooks that contain a crazy number of sheets with complicated names you might be daunted by the idea of creating a list of the sheet names.
The Good News
Next week I’ll show you how to extract a list of your sheet tab names with some dead easy VBA. Seriously, it’s copy and paste kind of stuff.