SUMPRODUCT多行列条件计算忽略空白避免#VALUE!错误的方法

问题原因 #

你当前公式触发#VALUE!错误的核心原因是：将布尔值判断数组直接和Data!$E$2:$JN$200数值区域做乘法运算时，该区域内的空白单元格（会被识别为空文本）、非数值类内容无法参与乘法计算，直接抛出错误。

解决方案 #

不需要修改源数据表的空白值，推荐使用SUMPRODUCT多参数写法，该函数原生支持自动忽略非数值类型的单元格内容，不会触发错误：

=SUMPRODUCT(Data!$E$2:$JN$200,--(Data!$A$2:$A$200=$B8),--(Data!$E$1:$JN$1=M$3))

写法说明 #

• 用逗号分隔三个独立参数，SUMPRODUCT会自动对三个数组对应位置的数值相乘后求和
• -- 是把条件判断返回的TRUE/FALSE布尔值转换为1/0数值，方便参与计算
• 源数据里的空白、文本类内容会被SUMPRODUCT默认当做0处理，不会抛出错误，完全不影响原有数据结构

备选兼容写法 #

如果习惯原有乘法逻辑的写法，可以套IFERROR处理异常值，Excel 365/2021可直接回车使用，更低版本需要按Ctrl+Shift+Enter数组确认：

=SUMPRODUCT(IFERROR(Data!$E$2:$JN$200*1,0)*(Data!$A$2:$A$200=$B8)*(Data!$E$1:$JN$1=M$3))

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