sumproduct多条件求和经典问题(乘号和逗号)剖析

SUMPRODUCT函数的含义为在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。按我的理解就是两个以上的数组乘积之和。例如，公式为：=SUMPRODUCT(A2:A4, B2:B4)就相当于=A2*B2+A3*B3+A4*B4，两个区域用逗号隔开，不过用乘号也是没有问题的。似乎也只有这个函数才有这种对应元素之间先行捉对计算的功能。

1、条件求和时条件区域与求和区域之间可以用逗号，也可以用乘号，因为无论是逗号还是乘号，都是将数组中对应元素相乘，再取乘积之和。但是，条件之间是必须用乘号的，如果用逗号则得到错误结果。例如，公式为：=SUMPRODUCT((A2:A9=A3)*(B2:B9=B3)*C2:C9)也可以写成：=SUMPRODUCT((A2:A9=A3)*(B2:B9=B3), C2:C9)，如果写成：=SUMPRODUCT((A2:A9=A3), (B2:B9=B3), C2:C9)，则结果错误，为什么呢？

首先我们把上面的问题简化，假定每个数组只有一个值，我们看看计算结果，即：

SUMPRODUCT({TRUE}*{TRUE}*{8})=8

SUMPRODUCT({TRUE}*{TRUE}, {8})=8

SUMPRODUCT({TRUE}, {TRUE}, {8})=0

注意，上面的公式中大括号表示一个数组，SUMPRODUCT函数处理每一个参数数组的的元素时，其中的非数值型值（文本、逻辑）是作为0来处理的，所以用逗号分隔时结果为0，如果用乘号，{TRUE}*{TRUE}作为一个表达式先行计算，其计算结果是1（按true=1，false=0计算）。

2、条件求和时，如果求和区域出现文本型值，则必须用逗号，用乘号则报错，我们看一下计算结果，即：

SUMPRODUCT({TRUE;FALSE}*{TRUE;TRUE}, {8;”song"})=8 

SUMPRODUCT({TRUE;FALSE}*{TRUE;TRUE}*{8;”song"})=#VALUE! 

同上，因为数组区域中的文本信息是作为0来处理的，所以第一个公式得到正确的结果。第二个公式因为要先计算表达式的值，所以碰到了文本作为乘数的情况，所以结果会报错。

3、条件求和时，如果求和区域不是一列而是一个矩形区域时，则必须用乘号，用逗号会报错。即

SUMPRODUCT((A2:A9=A3)*(B2:B9=B3)*C2:F9)=正常值

SUMPRODUCT((A2:A9=A3)*(B2:B9=B3), C2:F9)=#VALUE! 

 

对于以上不同的公式写法，可以用“公式求值”来看看公式的运算情况，从而探知为什么会出现这样的结果。

综上所述，SUMPRODUCT函数各数组间尽量用逗号隔离，条件求和的标准写法应该是：

SUMPRODUCT((条件1)*(条件2)*(条件3)*…*(条件n), 求和区域)

————————————————

原文链接：https://blog.csdn.net/iamlaosong/article/details/54315876