Sum Across Multiple Tabs, Only If Tab is Flagged as being Active-CodePudding

I have 5 tabs: "Start" "2020" "2019" "2018" "End" I would like to sum the same cell (eg. cell A2) across all tabs, ONLY if the tab is flagged as being Active (eg. cell A1 of each tab will have 1 or 0 depending on if it's active).

Normally, I would just do: =sum('Start:End'!A2), however I ONLY want to sum the cells of the tabs that are Active. I want to be able to then drag this formula across so that all tabs' A2, B2, etc. can be summed up if they are a part of an Active tab.

I also need it to be flexible so that if I add a new tab before the "End" tab, I can easily have that incorporated into the sum formula.

I believe this can be achieved through a combination of Sumproduct and Sumifs, however I haven't been able to nail down the formula. I tried

=SUMPRODUCT(SUMIFS('Start:End'A2,'Start:End'$A$1,"=1"))

however that returns #VALUE! error.

CodePudding user response：

With ms365 you could try Something like:

=REDUCE(0,{"Start","2020","2019","2018","End"},LAMBDA(a,b,a INDIRECT(b&"!A1")*INDIRECT(b&"!A2")))

Or, as per the given link by @ScottCraner:

=SUM(SUMIF(INDIRECT({"Start","2020","2019","2018","End"}&"!A1"),1,INDIRECT({"Start","2020","2019","2018","End"}&"!A2")))

which, with LET() becomes a bit less verbose:

=LET(X,{"Start","2020","2019","2018","End"},SUM(SUMIF(INDIRECT(X&"!A1"),1,INDIRECT(X&"!A2")))

CodePudding user response：

Since the number of entries is unlikely to surpass the character limitations on TEXTJOIN and FILTERXML, a non-volatile option would seem preferable. What's more, this also allows the desired 3D sheet referencing.

=SUM(FILTERXML("<a>"&TEXTJOIN("",,Start:End!A1:A2)&"</a>","//b[preceding::*[1]=1 and position() mod 2 = 0]"))

I have assumed that all entries in cell A2 are non-empty across all sheets. If this is not the case, amend to:

=SUM(FILTERXML("<a>"&TEXTJOIN("0",0,Start:End!A1:A2)&"</a>","//b[preceding::*[1]=1 and position() mod 2 = 0]"))