2

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.

user18729347
  • 43
  • 3
  • 2
    You have to use the indirect method and list the sheets when using SUMIFS: https://exceljet.net/formula/3d-sumif-for-multiple-worksheets SUMIFS does not allow array type inputs which `'Start:End'A2` is. – Scott Craner Apr 06 '22 at 20:50

2 Answers2

3

Assuming:

  • All cells A1 are either empty (thus zero) or a literal 0 or a 1;
  • Access to ms365's TOCOL().

Try:

=SUM(TOCOL(Start:End!A1)*TOCOL(Start:End!A2))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thank you! Is there any way to include all the tabs between "Start" and "End" without manually specifying each of the tabs? The motivation behind it is that I will be adding a lot of tabs in between "Start" and "End" for efficiency. – user18729347 Apr 06 '22 at 21:38
  • 1
    Great! Assuming access to that function even difficult 3D scenarios are now relatively straightforward. Still not sure why MS haven't introduced explicit functions for multi-sheet interrogations, but shouldn't really complain with all these great new functions! – Jos Woolley Apr 07 '22 at 09:20
  • @user18729347, yes there actually is. I edited my answer based on insight from that of Jos's contribution. – JvdV Apr 07 '22 at 09:37
  • Thank you so much, @JvdV! This seems like this would be a potentially great solution. I tried it, however it turned a #NAME? error even though I have Microsoft 365 - any idea why Excel isn't recognizing it? – user18729347 Apr 08 '22 at 02:56
  • 2
    Yes, it is showing `#NAME?` error since you are not in **Office Insiders Beta Channel Version** , just `Goto` --> `File` --> `Account` --> **Office Insider** it will be on right side, click drop down, and change channel and enable **Beta** wait for few minutes it will be activated if you are in `2204` or `2203` build version – Mayukh Bhattacharya Apr 08 '22 at 03:01
1

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><b>"&TEXTJOIN("</b><b>",,Start:End!A1:A2)&"</b></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><b>"&TEXTJOIN("</b><b>0",0,Start:End!A1:A2)&"</b></a>","//b[preceding::*[1]=1 and position() mod 2 = 0]"))

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
  • I had no idea the range of sheets could be captured through TEXTJOIN() like this. Well done! I learned a thing or two. It gave me the idea to use `TOCOL()`. – JvdV Apr 07 '22 at 09:12