Excel SUMPRODUCT忽略非数值仍返回#VALUE!，求兼容2016及以下版本方案

一、先排查核心错误诱因 #

• 检查运算/求和区域（比如公式中对应的数值列）是否包含非数值文本（除用于判断的"-"外），例如空格、特殊符号、文本格式的数字。
• 确认条件区域与运算区域的行列数完全匹配，比如C5:C513对应的数据区域必须也是5到513行，避免数组长度不匹配导致错误。

二、具体替代公式方案 #

方案1：SUM+IF数组公式（需按Ctrl+Shift+Enter确认） #

如果原SUMPRODUCT公式类似=SUMPRODUCT((C5:C513<>"-")*D5:D513)，可替换为以下两种写法：

=SUM(IF(C5:C513<>"-", D5:D513, 0))

或针对数值判断的版本：

=SUM(IF(ISNUMBER(C5:C513), D5:D513, 0))

注意：Excel 2016及更低版本中，输入完公式后必须按Ctrl+Shift+Enter组合键确认，公式会自动被大括号{}包裹，请勿手动输入大括号。

方案2：修正SUMPRODUCT公式，强制转换数值 #

若错误源于运算区域存在文本型数字或隐藏字符，用--或VALUE()强制转换为数值：

=SUMPRODUCT((C5:C513<>"-")*--D5:D513)

或：

=SUMPRODUCT((ISNUMBER(C5:C513))*VALUE(D5:D513))

说明：--和VALUE()可将文本型数字转为数值，避免文本参与乘法运算触发#VALUE!错误。

方案3：使用SUMIFS（单条件场景更稳定） #

如果仅需单条件求和，SUMIFS无需数组快捷键，兼容性更强：

=SUMIFS(D5:D513, C5:C513, "<>-")

若针对数值判断，可调整条件：

=SUMIFS(D5:D513, C5:C513, ">0") // 可根据C列实际数值范围调整条件

三、辅助排查技巧 #

• 用ISERROR()定位错误单元格：输入=ISERROR(D5)并下拉，返回TRUE的单元格即为问题所在。
• 清除隐藏字符：选中目标区域，用=CLEAN(C5)或=TRIM(C5)处理后复制为数值，再重新应用公式。

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