SUMPRODUCT函数三大经典案例

关键词：条件计数，条件求和

SUMPRODUCT函数案例：

假设下方是某公司大区业务员在各月的销售记录表格，其中A列是业务销售月份，B列是业务所属大区，C列是业务员姓名，D列是业务员的工资。

案例

案例1：百变小樱共领取几次工资？

我们先要确定题中的两个条件，一个是百变小樱，一个是工资出现次数。由此可知，这是一道单条件计数问题，通常我们都是用countif函数，那如何运用Sumproduct单条件计数呢，如下所示：

案例1

案例1解析：=SUMPRODUCT(($C$2:$C$7="百变小樱")*1)

首先我们判断$C$2:$C$7是否是“百变小樱”，如果是则返回逻辑值TRUE，不是则返回逻辑值FALSE，此时 $C$2:$C$7="百变小樱" 计算后结果为：{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}；因SUMPRODUCT函数的特性是将非数值型数组元素作为0处理，故此时我们需要将其在后方 *1 ，将逻辑值转化为数值，则为{0;1;0;0;0;1}，然后SUMPRODUCT函数计算其乘积和，结果为2。

此处有个小知识点：当逻辑值（TRUE、FALSE）参与计算时会转化为数值。TRUE=1，FALSE=0，TRUE*TRUE=1，TRUE*FALSE=FALSE*TRUE=0，FALSE*FALSE=0

案例2：灰原哀共领取工资多少钱？

我们仍先确定题中的两个条件，一个是灰原哀，一个是共领取工资多少。由此可知，这是一道单条件求和问题，通常我们都是用sumif函数，那如何运用Sumproduct单条件求和呢，如下所示：

案例2

案例2解析：=SUMPRODUCT(($C$2:$C$7="灰原哀")*$D$2:$D$7)

首先我们判断$C$2:$C$7是否是“灰原哀”，如果是则返回逻辑值TRUE，不是则返回逻辑值FALSE，此时 $C$2:$C$7="灰原哀" 计算后结果为：{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}；SUMPRODUCT函数将其逻辑值与$D$2:$D$7的值相对应乘积求和,则为{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}*{5923;5463;10123;13393;9566;12641}= FALSE *5923+ FALSE *5463+ TRUE *10123+FALSE*13393+ TRUE *9566+FALSE*12641=0+0+10123+0+9566+0,求出结果为19689元。

案例3：3月份华北区和华南区共发放工资多少钱？

我们仍先确定题中的三个条件，一个是3月份，一个是华北区和华南区，还有一个条件是共发放工资的钱数。在前面案例中我们学习了在SUMPRODUCT函数中计算单条件求和，此题中我们遇到了多个大区的多条件求和。那如何计算出两大区的3月份发放工资呢？如下所示：

案例3

案例3解析：

方法1：=SUMPRODUCT(($A$2:$A$7="3月")*($B$2:$B$7="华北区"),$D$2:$D$7)+SUMPRODUCT(($A$2:$A$7="3月")*($B$2:$B$7="华东区"),$D$2:$D$7)

方法2：=SUMPRODUCT(($A$2:$A$7="3月")*($B$2:$B$7={"华北区","华东区"})*$D$2:$D$7)

方法1中首先判断$A$2:$A$7是否是3月， $B$2:$B$7是否是华北区，然后与求和区域相对应成绩求和 + 判断$A$2:$A$7是否是3月， $B$2:$B$7是否是华东区，然后与求和区域相对应成绩求和 ，求出答案为28687元。

上述公式是SUMPRODUCT函数最经典常见的用法：=SUMPRODUCT((条件1)*(条件2)……,求和区域)

但是，如果只有两个大区时，我们可以这么写，如果计算五个大区，十个大区呢，如果还如方法1这样书写，不仅电脑的数据计算量加大，而且我们要套上又多又冗长的公式。此时，我们就可以使用另一个SUMPRODUCT函数的经典用法：

=SUMPRODUCT((条件1)*( 条件区域={“条件，条件…”})*求和区域)

即方法2中的答案，求出答案为28687元。

课后划重点：

① SUMPRODUCT函数解析：

SUM在函数中是求和，PRODUCT在函数中是乘积，SUMPRODUCT函数的意义为乘积之和，公式即：SUMPRODUCT（array1,array2,array3,...）,其中array1,array2,array3,...为数组，将需要的各数组进行相乘并求和。

② SUMPRODUCT函数数组参数必须具有相同维数；

③ SUMPRODUCT函数将非数值型数组元素作为0处理；

④ SUMPRODUCT函数数据区域不能整列引用;

⑤ SUMPRODUCT函数在运用时，数据中不能出现错误值#N/A，否则公式返回值为错误值#N/A。

SUMPRODUCT函数的三大经典案例，小伙伴er萌，你萌学斐了嘛~