sumproduct函数，全部9种用法都在这

Excel函数 2023-01-08

我们在处理日常工作的时候，函数是一个不可缺少的部分，Excel中除了有vlookup等万能查询函数，还有我们必须要熟悉的sumproduct函数，它可以实现求和、单一多条件和复杂情况下的各类计数及综合排名等数据处理，今天我们就来学习一下这个函数的全部9种用法。

用法1：简单数组求和

案例：求出所有人员最终的补贴之和

函数1=SUMPRODUCT(D3:D8,E3:E8)

函数2=SUMPRODUCT(D3:D8*E3:E8)

案例讲解：sumproduct函数将相应元素之间通过相乘并求和计算，可以用逗号或是用*号进行连接，返回相应的数组或区域乘积的和。数组设计的区域必须是相同的，如基础补贴区域为：D3:D8，难度系数选择的范围也是3-8。这里实现的效果也可以是将每一个值相乘后再相加，结果都是一样的5960。

用法2：别具一格的单一条件计数

案例：求出男女人数

男=SUMPRODUCT(N($D$3:$D$8=H5))

女=SUMPRODUCT(N($D$3:$D$8=H6))

案例讲解：在计数的时候我们在中间使用了N函数，这个函数代表将True的值转化为1，将False的值转换为0，最后sumproduct函数将所有符合条件的值进行求和。我们可以选择N($D$3:$D$8=H5)函数之后，按F9进行函数解析为：SUMPRODUCT({1;0;1;1;0;1})。

用法3：比sumifs更简单的多条件数据求和

案例：求出男员工中难度系数在1以上的总的工作完成度。

函数=SUMPRODUCT(($D$4:$D$9="男")*($F$4:$F$9>1)*($E$4:$E$9))

函数解析：在多条件求和中，我们的操作方法跟用法1一致，将多个条件用*进行连接即可实现。

用法4：比countifs看起来更舒服的多条件计数

案例：求工作完成度大于5的男员工人数。

函数=SUMPRODUCT(($D$4:$D$9="男")*($E$4:$E$9>5))

案例讲解：操作方法同用法3，唯一的不同是后面没有再*数值，所以我们最终的结果只是将符合条件的个数进行求和。选择($D$4:$D$9="男")*($E$4:$E$9>5)，按F9最终的结果会解析为如下：

用法5：不一样的综合多参数综合权重排名

案例：将人员按照工作完成度、执行力、满意度等不同维度占比进行综合排名。

函数=SUMPRODUCT($D$3:$F$3,D4:F4)

案例讲解：首先通过用sumproduct函数进行综合得分的计算，最后用RANK函数进行最终的数据排名。

用法6：不同条件下的跨列数据求和

案例：计算第一季度每个人的目标值及最终完成值。

目标=SUMPRODUCT((D$3:I$3=$J$3)*($D4:$I4))

实际=SUMPRODUCT((D$3:I$3=$K$3)*($D4:$I4))

案例讲解：在多条件求和的情况下，这个函数会比sumifs来的更加简单。

用法7：最快速度的数据拆分展示

案例：将左边按竖排展示的数据最快转换为右边的多维数据展示

函数=SUMPRODUCT(($B$3:$B$14=$G3)*($C$3:$C$14=H$2)*($D$3:$D$14))

案例讲解：这个方法与多条件数据求和方法一样，利用好相对引用和绝对引用就可以实现数据的最快速度转化显示。

用法8：求出销量排名前三产品的总销售量

案例：求出A-G产品中销量排名前三的总销售量

函数=SUMPRODUCT(LARGE($C$4:$C$10,ROW(1:3)))

案例解析：在这里我们使用了一个返回值的函数LARGE函数，他的作用在于可以返回区域中排名多少位的值。我们通过用ROW来返回1-3对应的数值，加上sumproduct函数的数组计算的特点，这样就可以实现排名前多少对应的总量。

用法9：求出今天仓库总共出库了多少种产品

函数=SUMPRODUCT(1/COUNTIF($C$3:$C$14,$C$3:$C$14))

案例讲解：在这里我们主要通过用countif函数计算出每种产品出现了多少次，再用1/countif，这样可以将出现的多的次数进行分解，选择countif函数按F9可以得到如下：

所以最后用1来除的时候，就可以将出现2次的改为2个1/2，出现3次的改为3个1/3。最后求和即可。