Google Sheets中SUMPRODUCT公式解析错误求助

错误原因分析 #

你的SUMPRODUCT公式存在几个关键问题：

• 维度不匹配：使用整列范围（如'3. Emp. Leave Setup'!B:B）和动态行范围（如B2:B）进行运算，两者维度不一致，触发解析错误。
• 冗余无效运算：公式末尾的'3. Emp. Leave Setup'!J:J*0完全多余，不仅会导致求和结果始终为0，还会干扰维度匹配逻辑。
• 不必要的DATEVALUE：如果'Yearly Clndr'!E2:E和F2:F已经是日期格式，使用DATEVALUE会触发错误（该函数仅能将文本格式的日期转换为日期值）。

修正后的SUMPRODUCT公式 #

结合原SUMIFS的逻辑，修正后的公式如下：

=SUMPRODUCT(
  ('3. Emp. Leave Setup'!B2:B1000=B2:B)*
  ('3. Emp. Leave Setup'!F2:F1000>='Yearly Clndr'!E2:E)*
  ('3. Emp. Leave Setup'!G2:G1000<='Yearly Clndr'!F2:F)*
  '3. Emp. Leave Setup'!J2:J1000
)

关键调整说明 #

• 将整列范围替换为具体行范围（如B2:B1000），确保所有运算范围维度一致，同时提升运算效率。
• 移除冗余的*0运算，直接将求和列J2:J1000作为最后一个参数参与运算，符合SUMPRODUCT的语法逻辑。
• 去掉不必要的DATEVALUE，直接使用日期单元格进行比较（若Yearly Clndr中的日期是文本格式，再保留DATEVALUE即可）。

替代方案：ARRAYFORMULA+SUMIFS实现数组运算 #

如果更倾向于保留SUMIFS的逻辑，可结合ARRAYFORMULA在Google Sheets中实现数组运算，公式如下：

=ARRAYFORMULA(
  SUMIFS(
    '3. Emp. Leave Setup'!J:J,
    '3. Emp. Leave Setup'!B:B, B2:B,
    '3. Emp. Leave Setup'!F:F, ">="&'Yearly Clndr'!E2:E,
    '3. Emp. Leave Setup'!G:G, "<="&'Yearly Clndr'!F2:F
  )
)

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