SUMPRODUCT函数中布尔数组求值问题及IF函数报错问询

嘿，这两个问题都是SUMPRODUCT使用中非常典型的细节坑，我来给你逐一拆解清楚：

疑问1：为什么=SUMPRODUCT($A$1:$A$5,$B$1:$B$5,IF($C$1:$C$5="b",1,0))返回#VALUE!错误？ #

核心原因在于SUMPRODUCT对参数数组的识别规则：

• SUMPRODUCT要求传入的参数是「内存数组」（可以直接被函数识别为数组的运算结果），而IF($C$1:$C$5="b",1,0)本质是一个数组公式逻辑，在普通输入模式下（不按Ctrl+Shift+Enter触发数组运算），Excel不会将其展开为完整的数组，反而会尝试返回单个值，导致SUMPRODUCT接收到的三个参数维度不匹配，最终抛出#VALUE!错误。
• 而--($C$1:$C$5="b")是通过双重负号直接将布尔值（TRUE/FALSE）转换为数值（1/0），这个运算会自动生成内存数组，不需要额外触发数组公式，SUMPRODUCT可以直接识别并完成计算。

如果一定要用IF函数实现，你需要按数组公式的方式输入：输入公式后按下Ctrl+Shift+Enter（新版Excel可能自动支持，但旧版必须手动触发），此时IF会返回完整的{0,1,0,1,0}数组，SUMPRODUCT就能正常计算得到38。

疑问2：=SUMPRODUCT($A$1:$A$5,$B$1:$B$5,$C$1:$C$5="b")的计算情况 #

这个公式的表现分两种场景：

在新版Excel（支持动态数组，如Excel 365/2021及以后）中 #

公式可以直接得到正确结果38。因为$C$1:$C$5="b"会返回布尔值数组（比如{FALSE,TRUE,FALSE,TRUE,FALSE}），SUMPRODUCT会自动将布尔值转换为对应数值（TRUE→1，FALSE→0），然后执行对应位置的乘积求和：
假设你的数据是：

计算过程就是：(2*5*0)+(3*6*1)+(4*7*0)+(5*4*1)+(6*9*0) = 0 + 18 + 0 + 20 + 0 = 38，和预期一致。

在旧版Excel（不支持动态数组，如Excel 2019及以前）中 #

这个公式会返回#VALUE!错误。因为旧版Excel不会自动将布尔数组转换为数值数组，SUMPRODUCT无法识别布尔值参与运算，必须手动用--或者N()函数将布尔值转换为1/0，也就是回到你最开始的正确公式写法。

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