跨不同工作表且数组原始长度不一致时的SUMPRODUCT函数使用问题求助
嘿，我完全懂你现在的困扰！用SUMPRODUCT跨两个工作表做条件求和乘积，明明按筛选条件处理后有效数据的长度是匹配的，但因为两个数组的原始行数不一样，直接写公式就是报错，太闹心了对吧？
先帮你理清楚核心需求：
你有两个表，Table1（Sheet1）是个体数据，Table2（Sheet2）是时间维度的投影数据。对Table1的每一行，你需要计算：
- 从Table1中筛选出状态（A列）匹配、性别（B列）匹配、年龄（C列）在当前行[C2, H2]区间的记录，将这些记录的factor(D)、weight(E)、reduction(F)、discount(G)列相乘；
- 同时从Table2中筛选出年龄（B列）等于当前Table1行的年龄、时间（A列）<=当前行H2+1的记录，将这些记录的rate(C)、count(D)列相乘；
- 最后把两组筛选后的乘积一一对应，再求和得到最终结果。
你原来写的公式之所以失效，就是因为SUMPRODUCT要求参与计算的所有数组必须原始长度完全一致——哪怕筛选后有效元素数量相同，只要原始行数不一样，它就无法正确匹配对应元素。
下面给你几种可行的解决方案，适配不同版本的Excel：
方法1：用动态数组函数（Excel 365/2021+ 推荐）
如果你用的是新版Excel，FILTER动态数组函数能帮你轻松筛选出符合条件的数组，确保两个筛选结果长度一致，直接就能用SUMPRODUCT计算：
=SUMPRODUCT( // 先计算Table1中符合条件的D*E*F*G的数组 (FILTER(Sheet1!$D$2:$D$2801,(Sheet1!$A$2:$A$2801=A2)*(Sheet1!$B$2:$B$2801=B2)*(Sheet1!$C$2:$C$2801>=C2)*(Sheet1!$C$2:$C$2801<=H2))* FILTER(Sheet1!$E$2:$E$2801,(Sheet1!$A$2:$A$2801=A2)*(Sheet1!$B$2:$B$2801=B2)*(Sheet1!$C$2:$C$2801>=C2)*(Sheet1!$C$2:$C$2801<=H2))* FILTER(Sheet1!$F$2:$F$2801,(Sheet1!$A$2:$A$2801=A2)*(Sheet1!$B$2:$B$2801=B2)*(Sheet1!$C$2:$C$2801>=C2)*(Sheet1!$C$2:$C$2801<=H2))* FILTER(Sheet1!$G$2:$G$2801,(Sheet1!$A$2:$A$2801=A2)*(Sheet1!$B$2:$B$2801=B2)*(Sheet1!$C$2:$C$2801>=C2)*(Sheet1!$C$2:$C$2801<=H2)))* // 再计算Table2中符合条件的C*D的数组 (FILTER(Sheet2!$C$2:$C$5051,(Sheet2!$B$2:$B$5051=C2)*(Sheet2!$A$2:$A$5051<=H2+1))* FILTER(Sheet2!$D$2:$D$5051,(Sheet2!$B$2:$B$5051=C2)*(Sheet2!$A$2:$A$5051<=H2+1))) )
这个公式会自动筛选出两个长度完全匹配的数组，直接完成乘积求和，不需要额外操作。
方法2：用INDEX+SMALL数组公式（旧版Excel适用）
如果是旧版Excel没有动态数组功能，就需要用INDEX+SMALL组合提取符合条件的行号，生成长度一致的数组。先定义一个辅助变量N（可以用单元格存储，比如K2）：
K2=COUNTIFS(Sheet1!$A:$A,A2,Sheet1!$B:$B,B2,Sheet1!$C:$C,">="&C2,Sheet1!$C:$C,"<="&H2)
这个N就是符合条件的记录数（你说筛选后两个表长度一致，所以Sheet2的符合条件记录数也等于N），然后用下面的数组公式（输入后按Ctrl+Shift+Enter确认）：
=SUMPRODUCT( INDEX(Sheet1!$D$2:$D$2801,SMALL(IF((Sheet1!$A$2:$A$2801=A2)*(Sheet1!$B$2:$B$2801=B2)*(Sheet1!$C$2:$C$2801>=C2)*(Sheet1!$C$2:$C$2801<=H2),ROW(Sheet1!$A$2:$A$2801)-ROW(Sheet1!$A$2)+1),ROW(INDIRECT("1:"&K2)))* INDEX(Sheet1!$E$2:$E$2801,SMALL(IF((Sheet1!$A$2:$A$2801=A2)*(Sheet1!$B$2:$B$2801=B2)*(Sheet1!$C$2:$C$2801>=C2)*(Sheet1!$C$2:$C$2801<=H2),ROW(Sheet1!$A$2:$A$2801)-ROW(Sheet1!$A$2)+1),ROW(INDIRECT("1:"&K2)))* INDEX(Sheet1!$F$2:$F$2801,SMALL(IF((Sheet1!$A$2:$A$2801=A2)*(Sheet1!$B$2:$B$2801=B2)*(Sheet1!$C$2:$C$2801>=C2)*(Sheet1!$C$2:$C$2801<=H2),ROW(Sheet1!$A$2:$A$2801)-ROW(Sheet1!$A$2)+1),ROW(INDIRECT("1:"&K2)))* INDEX(Sheet1!$G$2:$G$2801,SMALL(IF((Sheet1!$A$2:$A$2801=A2)*(Sheet1!$B$2:$B$2801=B2)*(Sheet1!$C$2:$C$2801>=C2)*(Sheet1!$C$2:$C$2801<=H2),ROW(Sheet1!$A$2:$A$2801)-ROW(Sheet1!$A$2)+1),ROW(INDIRECT("1:"&K2)))* INDEX(Sheet2!$C$2:$C$5051,SMALL(IF((Sheet2!$B$2:$B$5051=C2)*(Sheet2!$A$2:$A$5051<=H2+1),ROW(Sheet2!$A$2:$A$5051)-ROW(Sheet2!$A$2)+1),ROW(INDIRECT("1:"&K2)))* INDEX(Sheet2!$D$2:$D$5051,SMALL(IF((Sheet2!$B$2:$B$5051=C2)*(Sheet2!$A$2:$A$5051<=H2+1),ROW(Sheet2!$A$2:$A$5051)-ROW(Sheet2!$A$2)+1),ROW(INDIRECT("1:"&K2))) )
这个公式通过SMALL提取符合条件的行号，再用INDEX把对应列的数值提取成长度为N的数组，确保两个数组长度一致后再计算乘积和。
方法3：辅助列+合并表法（最直观，适合新手）
如果觉得上面的公式太复杂，可以用更简单的思路：
- 在Sheet1新增辅助列I，计算每行的
D*E*F*G：=D2*E2*F2*G2，下拉填充； - 在Sheet2新增辅助列E，计算每行的
C*D：=C2*D2，下拉填充； - 新建一个空白工作表，用
FILTER（或高级筛选）把Sheet1中符合条件的记录和Sheet2中符合条件的记录分别提取出来，确保两行数一致； - 在新表中直接计算两列的乘积之和：
=SUMPRODUCT(新表!A:A,新表!B:B)
这种方法虽然步骤多，但逻辑清晰，不容易出错，适合不想写复杂公式的情况。
你提到自己已经找到了解决方案，也欢迎分享出来让更多人参考哦！
备注：内容来源于stack exchange，提问作者jwcane
