Excel跨多工作表多列多条件SUMPRODUCT求和方案求助

• 有12个工作表，命名为Jan 2022至Dec 2022（对应2022年1-12月）
• 每个工作表的B列为「工作组」，E:J列为估算数值，第2行记录工作周
• 汇总表要求：A列为工作组，第1行为工作周，B2:BB25区域需填充对应工作组+工作周的估算值总和
• 已将12个工作表名称定义为命名区域SheetList

当前方案的局限 #

当前使用公式：

=SUMIFS('Jan 2022'!E:E,'Jan 2022'!$B:$B,$A2)

该公式能得到单月单周的正确结果，但需手动为每个工作周匹配对应工作表的列；遇到跨月工作周时，还需叠加多个SUMIFS公式，操作繁琐且易出错。

尝试的SUMPRODUCT公式及问题 #

尝试了3个SUMPRODUCT公式均未解决问题：

1. 公式：

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList[Sheet Names]&"'!$E:$E"),INDIRECT("'"&SheetList[Sheet Names]&"'!$B:$B"),$A2))

仅能按单个工作组跨所有月份求和，无法添加工作周筛选条件；且每个单元格需嵌套6个SUMPRODUCT公式，效率极低。

2. 公式：

=SUMPRODUCT((INDIRECT("'"&SheetList[Sheet Names]&"'!$E$5:$J$1000"))*(INDIRECT("'"&SheetList[Sheet Names]&"'!E$2:$J$2")=B$1)*(INDIRECT("'"&SheetList[Sheet Names]&"'!$B$5:$B$1000")=$A2))

返回#VALUE错误，原因是多工作表引用导致数组维度不匹配，SUMPRODUCT无法直接处理此类多维数组。

3. 公式：

=SUMPRODUCT(N(INDIRECT("'"&SheetList[Sheet Names]&"'!$E$5:$J$1000"))*(N(INDIRECT("'"&SheetList[Sheet Names]&"'!$E$2:$J$2"))=B$1)*(N(INDIRECT("'"&SheetList[Sheet Names]&"'!$B$5:$B$1000"))=$A2))

返回结果为0，N函数转换后仍未正确匹配跨工作表的条件，逻辑失效。

解决方案 #

通用Excel版本公式 #

在汇总表的B2单元格输入以下公式，然后向右向下填充：

=SUMPRODUCT(SUMIFS(INDEX(INDIRECT("'"&SheetList&"'!$E:$J"),,MATCH(B$1,INDIRECT("'"&SheetList&"'!$E$2:$J$2"),0)),INDIRECT("'"&SheetList&"'!$B:$B"),$A2))

公式原理：

1. INDIRECT("'"&SheetList&"'!$E:$J")：批量引用所有月份工作表的E-J数据区域
2. MATCH(B$1,INDIRECT("'"&SheetList&"'!$E$2:$J$2"),0)：在每个工作表的第2行（工作周行）匹配当前汇总表的工作周，返回对应列的位置（1-6）
3. INDEX(..., , MATCH(...))：定位到每个工作表中对应工作周的整列
4. SUMIFS(..., INDIRECT("'"&SheetList&"'!$B:$B"), $A2)：在每个工作表中，对匹配当前工作组的行求和
5. SUMPRODUCT(...)：汇总所有工作表的求和结果，得到跨月的最终总和

Excel 365/2021简化公式 #

如果使用Excel 365或2021，可使用动态数组特性简化公式：

=SUM(SUMIFS(INDEX(INDIRECT("'"&SheetList&"'!$E:$J"),,XMATCH(B$1,INDIRECT("'"&SheetList&"'!$E$2:$J$2"))),INDIRECT("'"&SheetList&"'!$B:$B"),$A2))

用SUM替代SUMPRODUCT，利用动态数组自动求和，逻辑更简洁。

内容的提问来源于stack exchange，提问作者J Hz