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

Plower

New Member
Joined
Jul 18, 2019
Messages
2
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]
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top