合并含INDEX MATCH的SUMIFS与ARRAYFORMULA时遇异常

核心问题 #

合并公式返回0的根本原因是SUMIFS无法直接在ARRAYFORMULA中进行数组迭代运算，加上原单单元格的INDEX/MATCH逻辑直接改成数组范围后，条件匹配逻辑失效，导致SUMIFS找不到符合条件的数据，最终返回0。另外，IF分支同时返回文本（"Adgroup"）和数值，虽不会直接导致0，但可能干扰数组运算的一致性。

解决方案 #

方法1：用SUMPRODUCT替代SUMIFS（兼容旧版Sheets） #

SUMPRODUCT支持数组运算，可直接在ARRAYFORMULA中实现逐行统计：

=ARRAYFORMULA(IFERROR(
  IF(VLOOKUP($A$2:$A&$B$2:$B, {PLANNER!$D:$D&PLANNER!$G:$G, PLANNER!$S:$S}, 2, FALSE) = "Y", 
     "Adgroup",
     SUMPRODUCT(
       DATA!$H:$H,
       --(DATA!$B:$B = VLOOKUP($B2:$B&" "&$D2:$D, {REFERENCES!$A$2:$A&" "&REFERENCES!$B$2:$B, REFERENCES!$C$2:$C}, 2, FALSE)),
       --(DATA!$D:$D = $E2:$E)
     )
  ),
  "error"
))

• -- 用于将布尔值转换为1/0，SUMPRODUCT会对符合所有条件的H列数值求和，实现与SUMIFS一致的效果。
• 建议将REFERENCES的范围改成固定区域（比如$A$2:$A$1000），避免全列引用拖慢运算。

方法2：用BYROW逐行处理（新版Sheets推荐） #

BYROW可对每一行单独执行原公式2的逻辑，彻底避免数组兼容问题：

=BYROW($A$2:$E, LAMBDA(row,
  IFERROR(
    IF(VLOOKUP(INDEX(row,1)&INDEX(row,2), {PLANNER!$D:$D&PLANNER!$G:$G, PLANNER!$S:$S}, 2, FALSE) = "Y",
       "Adgroup",
       SUMIFS(DATA!$H:$H,
              DATA!$B:$B, INDEX(REFERENCES!$C:$C,MATCH(INDEX(row,2)&" "&INDEX(row,4), REFERENCES!$A:$A&" "&REFERENCES!$B:$B, 0)),
              DATA!$D:$D, INDEX(row,5)
       )
    ),
    "error"
  )
))

• BYROW遍历每一行数据，用INDEX提取当前行的A、B、D、E列值，完全复用原两个公式的逻辑，兼容性更好，结果更准确。

额外注意事项 #

• 检查REFERENCES表中B列&" "&D列的拼接结果是否和DATA表的B列值完全一致（包括空格、大小写、特殊字符），匹配失败也会导致SUMIFS返回0。
• 避免全列引用（比如$A:$A），尽量用实际数据范围，提升运算速度和稳定性。

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