SUMPRODUCT函数应用
——SUMPRODUCT函数在数据汇总中的使用
如果你问我，工作中最喜欢的一个函数是什么
不是VLOOKUP，也不是OFFSET
我选择SUMPRODUCT
能计数，能求和，还能匹配
没有什么汇总问题，不是一个SUMPRODUCT解决不了的
1、什么是SUMPRODUCT？
SUMPRODUCT函数，从字面上看包括SUM（求和）、PRODUCT（乘积），顾名思义，即先乘积后求后，它可以返回两个或两个以上数组或区域的对应位置乘积的和。
比如，上图中，用SUMPRODUCT函数对A1:A4和B1:B4两个区域进行先乘积后求和，相当于34*3 456*5 12*5 567*7，结果为6411。
但如果只是用SUMPRODUCT来做这样的简单运算，那实在有点“暴殄天物”了。SUMPRODUCT能做的，远远不止这些，不信来看。
▼
2、SUMPRODUCT用于条件统计
由于SUMPRODUCT函数的参数是数组，因此当参数中包含逻辑数组（即由TRUE和FALSE组成的数组）时，通过运算（TRUE可以转化为1，FALSE可以转化为0）可以实现数据的条件计数和求和。
比如，下面这个案例，表格中记录的是一个学校全部老师的性别、职称和每周课时情况。
现在，要求根据性别和职称统计老师人数和课时。
以B26和B31单元格为例，请注意SUMPRODUCT函数的写法：
公式中，$B$12:$B$22=$A26和$C$12:$C$22=$B$25为数组判断，自上而下判断数据行是否为“男”性以及是否是“高级”职称，返回的结果为逻辑数组，用*做乘法运算后转化为数值型数组（即由0、1组成），如下图所示。
所以，总结SUMPRODUCT函数用于条件计数和求和，通用公式如下 ▼
条件计数
=SUMPRODUCT（（条件一）*（条件二）*（条件三）*……）
条件求和
=SUMPRODUCT（（条件一）*（条件二）*（条件三）*……，求和列）
使用SUMPRODUCT函数进行条件统计，优势明显：
1、完成可以替代条件计数（COUNTIF/COUNTIFS）、条件求和（SUMIF/SUMIFS）函数，同时使用更方便、易记；
2、更容易写判断条件，结合其它函数可以用于复杂场景下的数据统计与汇总。
下面，我来举几个最近函数课程和实际工作中遇到的案例。
▼
3、案例 | SUMPRODUCT中“或”的条件写法
这个案例来自于卓越之道视频课程《Excel函数从入门到精通，助你成为职场Excel达人》。
如下图报表为卓越之道在运营初期（2016/4/9到2016/9/7）的粉丝增长数据，共计154条记录。
现在我们要求根据这张表计算2016年5月份和8月份净增关注人数（D列）的总和，请问如何计算？
这里涉及到两个条件，一是A列日期为5月份的数据，二是A列数据为8月份的数据，是“或”的关系。可能你想到的是SUMIFS，但是使用SUMIFS很难定义条件（你可以试试），而用SUMPRODUCT函数则可以快速进行计算，公式如下：
=SUMPRODUCT(((MONTH($A$5:$A$158)=5) (MONTH($A$5:$A$158)=8)),$D$5:$D$158)
其中，两个条件之间用加号 进行连接，这样两个逻辑数组对应位置只要有一个为TRUE，相加后结果就为1，最后再与数值数组$D$5:$D$158相应位置进行先乘积后求和即得到最后的计算结果。
所以，对于逻辑为“或”的多条件之间可以用加号 ；对于逻辑为“并”的多条件之间可以用乘号*，目的都是通过运算将逻辑值转化为1和0。
4、案例 | SUMPRODUCT用于数值匹配
最近，我在负责公司战略任务评估，根据战略管理办法，一项部门任务根据承办性质（主办/协办）和任务类型（一类/二类/三类）对应不同的评分标准。比如，由部门主办的一类任务标准得分为10分，而协办的一类任务得分为5分，具体得分表如下：
现在，我需要根据任务的承办性质（B列）和任务类型（E列）快速将上述基准分值匹配到各部门的任务表（L列）中，如下图所示：
按照常规匹配办法，我们必须先对基准得分表进行处理，将A、B两列进行连接得到一个新的表，然后用VLOOKUP函数进行匹配。
但是，如果熟练掌握SUMPRODUCT函数，就不必这么麻烦了，直接编写公式如下：
公式“=SUMPRODUCT(($A$40:$A$45=$B4)*($B$40:$B$45=$E4)*($C$40:$C$45))”实际上就是前述的第2个通用公式=SUMPRODUCT（（条件一）*（条件二）*（条件三）*……，求和列）。
表面上看好像是做了一个数据匹配，本质上还是条件求和。
5、案例 | 判断条件中的辅助函数应用
这个案例即昨天我们推文中的案例，再看一遍题目。
为了跟进2017年公司项目的推进情况，进行量化评估，我设计了下面这张表，让需求部门按项目对承办部门（包括主办、协办）进行打分。
最终收到需求部门打分见L列“综合评分”。
现在的问题是，我需要进一步按规则将评分分配到每个承办部门，并按部门进行汇总。分配规则是：主办部门拿满分，协办部门拿总分的30%。比如针对项目1，产品部作为主办部门得分为9分，而销售部、市场部、中收部作为协办部门各拿30%，即2.7分。
每个部门既有可能是主办，也有可能是协办，所以需要按部门对主办分和协办分各自按部门进行汇总。最终要完成的汇总格式如下表：
这里有两点要注意：
1、表格中的协办（F列），一个单元格中包含多个部门；
2、报表中部门名字写法可能没有统一，比如同一个部门，有些地方写的是风险部，有的地方写的可能是风险管理部。
昨天的文章发出后，很多朋友都下载文件进行了尝试，比如这样：
或者这样：
虽然可能得出了结果，但想必是废了九牛二虎之力，使用了中间辅助过程。
好吧，下面又到SUMPRODUCT函数隆重登场了，我们来看如何用SUMPRODUCT一步到位解决这一问题。
▼
1）主办分汇总
2）协办分汇总
显然，不管是主办还是协办，我们的汇总思路都相同，即从项目打分表的E、F两列判断每一个项目是否包含要汇总的部门。转化成公式条件，就用到了ISNUMBER加FIND函数，如果FIND返回的是一个数值，证明满足包含关系，则通过SUMPRODUCT实现得分求和。
类似的数据场景在我们平时的工作中屡见不鲜，只要熟练掌握SUMPRODUCT函数，能够灵活用辅助函数实现条件编写，就可以轻松应对各种数据汇总问题。可以说，没有什么汇总的问题，不是一个SUMPRODUCT解决不了的。
当然，这个案例也有其它解法，我们也可以通过通配符技巧快速进行汇总（关于通配符技巧，可以参见《这两个字符，你真的会用吗》），公式如下：
B16单元格：=SUMIF($E$2:$E$11,''*''&A16&''*'',$L$2:$L$11)
C16单元格：=SUMIF($E$2:$E$11,''*''&A16&''*'',$L$2:$L$11)*0.3
以上就是今天的内容，如果你觉得有用，欢迎点赞、收藏、转发~
案例文件获取
卓越之道会员请在会员群获取
非会员请在后台回复「315」获取下载地址
案例文档 ▼
联系客服
