Search code examples
excelexcel-formulasumsumifssumproduct

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

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.

Solution

  • 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))