我有5个标签：“ start”“ 2020”“ 2019”“ 2018”“ end”
我想在所有选项卡上总结相同的单元格（例如单元格A2），只有将选项卡标记为处于活动状态时（例如，每个选项卡的单元格A1具有1或0，具体取决于它是否有效）。
通常，我只会做：= sum（'start：end'！a2），但是我只想总结有效的选项卡的单元格。我希望能够将此公式拖动越过，以便如果它们是活动选项卡的一部分，则可以将所有选项卡的A2，B2等拖动。
我还需要灵活，因此，如果我在“ end”选项卡之前添加一个新选项卡，我可以很容易地将其合并到总和公式中。
我相信这可以通过Sumproduct和sumifs的结合来实现，但是我无法确定公式。 但是，我尝试了
=SUMPRODUCT(SUMIFS('Start:End'A2,'Start:End'$A$1,"=1"))
#value！错误。
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.
发布评论
评论（2）
Assuming:
A1are either empty (thus zero) or a literal 0 or a 1;尝试：
Assuming:
A1are either empty (thus zero) or a literal 0 or a 1;TOCOL().Try:
由于条目数量不太可能超过
TEXTJOIN和FILTERXML的字符限制，因此非易失性选项似乎更可取。此外，这还允许所需的 3D 图纸参考。=SUM(FILTERXML(""&TEXTJOIN("",,Start:End!A1:A2)&"","//b[前面::*[1]=1 且position() mod 2 = 0]"))我假设单元格
A2中的所有条目在所有工作表中都非空。如果不是这种情况，请修改为：=SUM(FILTERXML(""&TEXTJOIN("0",0,Start:End) !A1:A2)&"","//b[前面::*[1]=1和position() mod 2 = 0]"))Since the number of entries is unlikely to surpass the character limitations on
TEXTJOINandFILTERXML, 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
A2are 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]"))