万能的SUMPRODUCT函数一个顶仨，不服都不行！ / 开普饭

点击上方

蓝色

文字关注我们吧！

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

大家熟悉的EXCEL函数公式肯定包括下面这些：IF函数，VLOOKUP函数，SUM函数等等。它们各有专长，功能都非常强大，且受人追捧。今天要向大家介绍的这个SUMPRODUCT函数，朴实低调，不为大多数人所知，但却同样拥有超强的能力！

以前有个旧帖子简单介绍过SUMPRODUCT函数的基本语法结构，大家可以参看本是同根生。在很多场合下它可以替代SUM函数、SUMIF函数、COUNT函数和COUNTIF函数等等。

下面就让我们一同来领略一下它的威力吧！

数据求和

这里的数据求和我们理解的用SUM函数求和还不太一样。请看下例。

在单元格A9中输入“=SUMPRODUCT(B2:B5,C2:C5)”即可。

可以看到，它是把两列数据区域中对应的数据相乘，再求总和。如果是使用SUM函数，需要先算出每一行的销售额，再求和算出总的销售额。

可以看出，SUMPRODUCT函数一步完成了SUM函数需要两步完成的任务，效率上秒杀SUM函数哦！

基础

应用

TIPs：如有多列数据，SUMPRODUCT搞得定！只需要添加新的数据区域即可，剩下的事情它会替你做好的！

条件求和

SUMPRODUCT函数的本领可不仅仅是这些哦。给它一定的条件，它就可以创造奇迹出来！

下例中求“冰箱”的销售总额。

在单元格A8中输入“=SUMPRODUCT((A2:A5="冰箱")*(B2:B5)*(C2:C5))”即可。

思路：

A2:A5="冰箱"返回一组新的数据{FALSE;TRUE;TRUE;FALSE}，False=0，True=1
三组数组相乘后得到正确的结果

拓展

应用

注意，若将公式改为“=SUMPRODUCT((A2:A5="冰箱")*(B2:B5),(C2:C5))”

也能得到正确的结果。为什么？

重点：

SUMPRODUCT函数有两种书写方式：

=SUMPRODUCT((条件1)*(条件2)*...*(条件n)*(求和区域))

=SUMPRODUCT((条件1)*(条件2)*...*(条件n)，(求和区域))

此例下上面两种公式的书写方式都可以得到正确的结果。

但当参数中含有逻辑值时，公式的书写必须用第一种方式；当参数中含有文本数据时，必须使用第二种方式。

条件计数

SUMPRODUCT函数的第三个大本领就是条件计数。下例中，求某产品出现的次数。

上面三组公式的原理都是相同的，都是利用的逻辑判断值乘以“1”后转换为“0”和“1”，再进行求和计算。

拓展

应用

TIPs：

和COUNTIF函数是高能低效相比，SUMPRODUCT函数更具有效率

模糊条件求和

EXCEL中有很多函数都是支持模糊查询的。下例中我们也可以在SUMPRODUCT函数中依据模糊条件来求和。

如何求出所有商品名中含有“冰”字的销售额？

在单元格A9中输入“=SUMPRODUCT(--ISNUMBER(FIND("冰",A2:A5))*(B2:B5),(C2:C5))”即可。

思路：

用FIND函数查找含有“冰”字的单元格。若单元格中不含有“冰”字则返回错误值#VALUE!
用ISNUMBER函数判断FIND函数的返回值是否为数字类型，返回值为TRUE或者FALSE
用--来将逻辑值转换为数值
用SUMPRODUCT函数求和

拓展

应用

注意：由于参数中含有文本数据，因此SUMPRODUCT函数在书写时使用第二种方式。

分类汇总求和（一）

在帖子利用模拟运算表进行【分类汇总】中介绍过利用SUMPRODUCT函数进行分类汇总求和。下面在详细分析一下这个技巧。

在单元格F2中输入“=SUMPRODUCT((YEAR($A$2:$A$17)=YEAR(F$1))*(MONTH($A$2:$A$17)=MONTH(F$1))*($B$2:$B$17=$E2)*($C$2:$C$17))”并向下向右拖曳即可。

思路：

利用YEAR函数、MONTH函数分别提取年份和月份的信息，并和分类月份对比判断
利用SUMPRODUCT函数分类求和

分类汇总求和（二）

下面这个例子，利用SUMPRODUCT函数进行分类汇总也具有一定的代表性。

在单元格中输入“=SUMPRODUCT((MONTH(A2:A17)=6)*(B2:B17={"东北","西北"})*(D2:D17))”即可。

思路：

这里B2:B17={"东北","西北"}通过手动输入区域名称，创建了一个新的数组参与运算

文章推荐理由：

SUMPRODUCT函数不是非常“著名”的函数，但是它的功能却不输于SUM函数、SUMIF函数、COUNT函数和COUNTIF函数等等；而且从上可知，它是一个万能函数，可以轻松处理很多问题！

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

万能的SUMPRODUCT函数一个顶仨，不服都不行！

相关推荐