Sum same cell in worksheets, but number of worksheets will vary

[Plower]

Hi,
Long time user, but new to posting.

My workbook is used to perform scenario modelling, and contains a number of worksheets, named 'Base', 'Adj1', Adj2', 'Adj3'........'Total', where Total is the sum of all preceding worksheets.

However, to complicate matters, I want the ability to 'turn-off' tabs from the sum, and so I have a front sheet with a list of the tabs (col. A), and a Yes/No option in col. B. I want to be able to change the tabs I am summing, so in one instance I will sum Base and Adj1, but another instance sum Base, Adj3 and Adj4.

Do you know of a function to add into the Total tab, which will sum only those tabs with a 'Yes' against their name?


Two additions, which I have tried to use:

- a simply If function in each tab, which returns a 1 or 0, dependant upon whether a Yes or No is selected in the front sheet.

- the following formula [[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IFERROR(INDEX(Adj,SMALL(IF(Adj[Include in Total?]="Yes",ROW(Adj)-2),ROW(1:1)),2),"")], which returns a list of only those tabs with a Yes against their name, i.e. being the tabs I need to sum.

To complicate matters further, there is no limit to how many Adj tabs there could be, i.e I want the user to be able to add further Adj tabs, without the function breaking. If this proves to be an issue, this can be limited.

Any help in greatly appreciated![/FONT]

 

[pbornemeier]

Failed Attempt:
This will add all of the A2 cells for the tabs that are physically on/between Sheet1 and Sheet7 (inclusive)
=SUM(Sheet1:Sheet7!A2)
I was hoping that it could be extended to
=SUMPRODUCT(Sheet1:Sheet7!A2,NamedRange) where NamedRange was an array of 0s & 1s that that you would create based on the number of sheets that you wanted to include, but SUMPRODUCT does not accept 3D ranges.

Klutzy workaround:
You could add 2 worksheets, Start and End then this formula:
=SUM(Start:End!A2)
the Start and End worksheets

It would be fairly simple to accomplish what you want using VBA, but I don't know how to do it using formulas.

 

[pbornemeier]

Some interesting things here: https://www.mrexcel.com/forum/excel-questions/54677-cell-ranges.html

 

[Plower]

Thanks,
This unfortunately doesn't take into account that I do not necessarily want to sum consecutive tabs, and that the list of tabs to sum will constantly change.

I ended up going another way:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=SUMPRODUCT(SUMIF(INDIRECT("'"&Adj[Tabs to sum]&"'!A2"),"Yes",INDIRECT("'"&Adj[Tabs to sum]&"'!"&ADDRESS(ROW(AX6),COLUMN(AX6),4))))


Where:
Adj is a table with tab names in one column, and a column called "Tabs to sum" with a Yes / No option. In each tab, cell A2 replicates the Yes / No option for that tab.
In this instance cell AX6 is the cell I'm summing[/FONT]