汇总SUMPRODUCT万能函数所有经典和重要用法，进阶职场Excel达人

文章最后有彩蛋！好礼相送！

Excel秘籍大全，前言

Excel中有不少万能函数，一个函数能顶多个函数，例如SUBTOTAL、AGGREGATE、SUMPRODUCT等。今天，小包老师来给大家介绍其中的SUMPRODUCT函数，其语法为：=SUMPRODUCT（数组1，……），表示在给定的几组数组中，将数组间相应的元素相乘，并返回乘积之和。

Excel秘籍大全，正文开始

1.SUMPRODUCT函数求和

SUMPRODUCT函数常规用法可以用来求和，如图1所示，求学生的三科总成绩，在E2单元格输入公式：=SUMPRODUCT(B2:D2)，并向下填充。

图1

图2

参数B2:D2构建一个数组，只有一个数组时，可以看做（B2:D2）*1,1本身作为隐藏的数组，与这个数组的每个元素对应相乘，然后再求乘积之和，也就是说公式写成：=SUMPRODUCT((B2:D2)*1)，结果依然不变，如图2所示。

2.SUMPRODUCT函数求销售额

如图3所示，每个产品的单价和销量都知道了，求对应的销售额（单价*销量），可以用SUMPRODUCT，在E2输入公式：=SUMPRODUCT(C2*D2)，向下填充即可。C2*D2可以看成单元素的两个数组相乘，然后再求乘积之和。

图3

3.SUMPRODUCT函数求总销售额

如果要求稍微变一下，用SUMPRODUCT函数求所有产品的总销售额，如图4所示，公式为：=SUMPRODUCT(C2:C8*D2:D8)。

图4

C2:C8形成数组{2500;1500;50;59;10;1900;2500}，D2:D8形成数组{23;25;45;49;80;10;6}，这两个数组相乘得到{57500;37500;2250;2891;800;19000;15000}，然后再对它进行求和。

4.单条件计数

SUMPRODUCT函数也可以用来计数，如图5所示，求初二一班的总人数，其公式为：=SUMPRODUCT((C15:C29=F15)*1)。

图5

C15:C29=F15形成逻辑值组成的数组{TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}，逻辑值进行四则运算，TRUE可以看作1，FALSE可以看作0，(C15:C29=F15)*1结果即为{1;0;1;1;0;0;1;1;0;0;1;0;1;0;1}，然后再对这个数组进行求和。

5.多条件计数

利用SUMPRODUCT函数进行多条件计数，如图6所示，求初二一班成绩大于75的总人数，其公式为：=SUMPRODUCT((C15:C29=F15)*(D15:D29>75))。

图6

参数中的两个判断条件都会形成TRUE和FALSE组成的数组，两个数组相乘结果为{0;0;1;0;0;0;1;1;0;0;1;0;0;0;1}，符合两个条件的即为1，不符合或者符合其中一个的为0。

6.单条件求和

SUMPRODUCT函数也可以用来单条件求和，如图7所示，求初二一班的总分数，其公式为：=SUMPRODUCT((C15:C29=F15)*(D15:D29))。

图7

C15:C29=F15形成逻辑值数组，班级不符合时为FALSE，符合时为TRUE，D15:D29形成分数组成的数组，D15:D29与0和1相乘，得到{65;0;91;59;0;0;90;95;0;0;89;0;49;0;88}，不满足班级条件元素为0，最后再进行求和。

7.多条件求和——“且”关系

SUMPRODUCT函数进行多条件求和，如图8所示，统计初二一班分数大于75的总分数，这里为两个条件，且两个条件必须都满足，其公式为：=SUMPRODUCT((C15:C29=F15)*(D15:D29>75)*(D15:D29))。

图8

C15:C29=F15和D15:D29>75两个判断条件形成逻辑值数组，两个条件数组相乘的结果为{0;0;1;0;0;0;1;1;0;0;1;0;0;0;1}，D15:D29形成分数数组{65;85;91;59;79;80;90;95;99;81;89;50;49;40;88}，这两个数组相乘结果为{0;0;91;0;0;0;90;95;0;0;89;0;0;0;88}，不符合条件的返回0，最后进行求和。

8.多条件求和——“或”关系

如图9所示，统计初二一班或者分数大于75的总分数，这里为两个条件，满足任意一个条件即可，其公式为：=SUMPRODUCT((((C15:C29=F15)+(D15:D29>75))<>0)*(D15:D29))。

图9

C15:C29=F15和D15:D29>75两个条件形成的逻辑值数值相加得到{1;1;2;1;1;1;2;2;1;1;2;0;1;0;2}，其中1为两个条件满足其中一个，2为两个条件都满足，0为两个条件都不满足，不为0的结果为{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}，用这个判断不为0的逻辑值数组和D15:D29相乘得到{65;85;91;59;79;80;90;95;99;81;89;0;49;0;88}，其中0为两个条件都不满足返回的值，其他为两个条件都满足或满足其中之一返回的值。

9.数据查询

如图10所示，根据姓名和月份查询对应的销量，其公式为：=SUMPRODUCT((A34:A38=F34)*(B33:D33=G34),B34:D38)。

图10

(A34:A38=F34)*(B33:D33=G34)会形成5行3列的数组{0,0,0;0,0,0;0,1,0;0,0,0;0,0,0}，B34:D38同样会形成5行2列的数组，两个数组相乘，结果为{0,0,0;0,0,0;0,75,0;0,0,0;0,0,0}，最后再求和。

10.统计非重复数量

如图11所示，统计非重复人数，其公式为：=SUMPRODUCT(1/COUNTIF(A42:A52,A42:A52))。

图11

COUNTIF(A42:A52,A42:A52)数组用法，查询每一个姓名的重复次数，结果为{3;2;2;2;2;3;2;2;2;2;3}，再用1除以这个数组，目的是为了让重复的人数总和为1，得到{0.333333333333333;0.5;0.5;0.5;0.5;0.333333333333333;0.5;0.5;0.5;0.5;0.333333333333333}，最后再进行求和。

以上，就是小包老师为大家总结的关于SUMPRODUCT函数的经典和重要使用方法，关注小包老师，后面持续为大家分享实用的Excel职场函数技巧。

以下《900套高逼格工作模板.xls 》免费下载，不收一分钱！

常用Excel

用Excel玩好报表

是必不可缺的技能

要知道一张好的图表

可以做到一图胜千言！

今天推荐的超实用干货是

《900套高逼格工作模板.xls 》

3.2G高逼格Excel可视化模板

制作精美 可直接套用

适合自用和内部培训使用

领取方式

关注我们

私信发送关键字：900

即可免费领取

资料来源于网络，公益分享，如有侵权，联系删除