Efficient Summing Across Tabs

[mountainman88]

I have inherited a huge financials file with hundreds of country P&L tabs which pull from a data table. These country tabs are then summarised by identically laid out region tabs using a basic sum function linking to the relevant cells on the country tabs

Eg. EMEA region tab cell A1 = Sum(France!a1,Germany!a1,Italy!a1)

Using this method if i was to add another country to EMEA it would be very cumbersome to update every formula to for example = Sum(France!a1,Germany!a1,Italy!a1,Denmark!a1)

Ive tried using 3DSUM and SUMPRODUCT methods but they slow the sheet down too much. Can any one think of a way to update the SUM functions easily for additional country tabs added to a region?

Thanks

 

[JoeMo]

Create 2 sheets. Name the leftmost one "Start" and the rightmost "End". Place all the country sheets between these two sheets, including any new ones you might add later.
In your region tab cell: =SUM(Start:End!A1)
EDIT: Forgot to mention, leave cell A1 in start and end tabs empty so they don't contribute to the sum.

 

[mountainman88]

Create 2 sheets. Name the leftmost one "Start" and the rightmost "End". Place all the country sheets between these two sheets, including any new ones you might add later.
In your region tab cell: =SUM(Start:End!A1)
EDIT: Forgot to mention, leave cell A1 in start and end tabs empty so they don't contribute to the sum.

Youre describing a 3dSum, as mentioned in my post this is too slow. I need an easy way to expand the simple sum formula.

thanks

 

[Joe4]

It sounds like you want to do a 3d Sum, without actually doing a 3d Sum.
It is hard to make inefficient designs run efficiently. By definitition, I think summing across multiple tabs is probably not that efficient.

Quite frankly, it appears to me that you are probably using the wrong tool for the job. This sounds like a job for a database (Access, SQL, etc).
Of course, in order to get that database to function efficiently, I think you would need to overhaul the design quite a bit, as it does not sound like it is a normalized structure.
You would not have multiple tables to hold similar data for different regions. You would only have one table of each type, and query what you need.

I don't know if that is a possibility for you to convert this to a database, but if at all possible, I would highly encourage it.

 

[JoeMo]

Maybe use ctrl+h (find and replace dialog box) to replace country1!A1,country2!A1, .... with country1!A1,country2!A1,country3!
in formulas and click on Replace All:

 

[mountainman88]

It sounds like you want to do a 3d Sum, without actually doing a 3d Sum.
It is hard to make inefficient designs run efficiently. By definitition, I think summing across multiple tabs is probably not that efficient.

Quite frankly, it appears to me that you are probably using the wrong tool for the job. This sounds like a job for a database (Access, SQL, etc).
Of course, in order to get that database to function efficiently, I think you would need to overhaul the design quite a bit, as it does not sound like it is a normalized structure.
You would not have multiple tables to hold similar data for different regions. You would only have one table of each type, and query what you need.

I don't know if that is a possibility for you to convert this to a database, but if at all possible, I would highly encourage it.

Thanks for the response, I'm inclined to agree that a database is the solution. Any ideas where to start?

The multi-tab view is an exec requirement unfortunately, rather than selecting countries from drop-downs.

 

[JoeMo]

Thanks for the response, I'm inclined to agree that a database is the solution. Any ideas where to start?

Did you see post #5?

 

[Joe4]

Thanks for the response, I'm inclined to agree that a database is the solution. Any ideas where to start?

There are lots of good articles and tutorials on the internet that can be found with a Google Search.
Here is one good one to get started: Database design basics

I cannot stress the importance of the Rules or Normalization in database design enough. If you follow them, you will be able to do most of what you want without much trouble.
If you do not, even seemingly simple tasks can become a nightmare! I learned this the hard way the first time I tried designing a database and was unfamiliar with the Rules of Normalization.

 

[mountainman88]

It sounds like you want to do a 3d Sum, without actually doing a 3d Sum.
It is hard to make inefficient designs run efficiently. By definitition, I think summing across multiple tabs is probably not that efficient.

Quite frankly, it appears to me that you are probably using the wrong tool for the job. This sounds like a job for a database (Access, SQL, etc).
Of course, in order to get that database to function efficiently, I think you would need to overhaul the design quite a bit, as it does not sound like it is a normalized structure.
You would not have multiple tables to hold similar data for different regions. You would only have one table of each type, and query what you need.

I don't know if that is a possibility for you to convert this to a database, but if at all possible, I would highly encourage it.

Did you see post #5?

find and replace would work for one cell then I'd have to drag across and down, was hoping for something a little more automated and less error-prone than this.

thanks

 

[Dave Patton]

A agree with the database suggestions.
If the data is not too extensive, you may be able to use one Data sheet and a Report page.
With good data, you could use Pivot Tables, Filters, Excel formulas, etc.

or
You can consider the following that uses a named list of relevant sheets.

3d Sumif 2021.xlsm
	A
10	12
11	12
Summary

Cell Formulas
Range	Formula
A10	A10	=SUMPRODUCT(N(INDIRECT("'"&Sheets&"'!A1")))
A11	A11	=SUMPRODUCT(N(INDIRECT("'"&Sheets&"'!"&CELL("address",A1))))

Named Ranges
Name	Refers To	Cells
Sheets	=Summary!$F$2:$F$4	A10:A11