Excel函数100问
Excel中有不少万能函数，一个函数能顶多个函数，例如VLOOKUP、OFFSET、SUBTOTAL、AGGREGATE、SUMPRODUCT等。它们各有专长，功能都非常强大，且受人追捧，今天Excel办公小课堂来给大家介绍其中的
SUMPRODUCT函数
，朴实低调，不为大多数人所知，但却同样拥有超强的能力！一共整理了10种经典案例，非常全面，建议先收藏，不然到时候需要就找不到了哦~
SUMPRODUCT函数：
返回相应的数据或区域乘积之和
功能：
SUMPRODUCT(数组1,数组2,…)
一、SUMPRODUCT函数第1种用法：
基础用法，乘积求和
目的：
根据每个商品的数量和单价，计算所有商品的总计
公式：
=SUMPRODUCT(C4:C12,D4:D12)
二、SUMPRODUCT函数第2种用法：
单条件求和
目的：
根据每个商品的数量和单价，计算行政部使用各商品总计
公式：
=SUMPRODUCT((C4:C12=”行政部”)*(E4:E12)*(F4:F12))
说明：
SUMPRODUCT函数(C4:C12=”行政部”)表示在C4:C12区域查询“行政部”，如果此区域有包含行政部，则通过行政部中数量列和单价列的数据进行相乘，也就是(E4:E12)*(F4:F12)，最后进行求和汇总。
三、SUMPRODUCT函数第3种用法：
多条件求和
目的：
根据每个商品的数量和单价，计算行政部6月份使用各商品总计
公式：
=SUMPRODUCT((C4:C12=”行政部”)*(D4:D12=”6月”)*(E4:E12)*(F4:F12))
说明：
SUMPRODUCT函数(C4:C12=”行政部”)*(D4:D12=”6月”)表示在C4:C12区域查询“行政部”，D4:D12区域查询“6月”如果两个区域同时包含行政部和6月，则通过行政部和6月的数量列和单价列数据进行相乘，也就是(E4:E12)*(F4:F12)，最后进行求和汇总。
四、SUMPRODUCT函数第4种用法：
单条件计数
目的：
根据两个班级总人数，统计男同学人数
公式：
=SUMPRODUCT(N(D4:D13=”男”))
说明：
SUMPRODUCT函数N(D4:D13=”男”)表示在D4:D13区域计算性别为“男”的个数，其中“性别”列是文本形式，则需嵌套N函数，表示返回转化为数值后的值，从而统计男同学的人数。
五、SUMPRODUCT函数第5种用法：
多条件计数
目的：
根据两个班级总人数，统计80分以上女同学人数
公式：
=SUMPRODUCT((D4:D13=”女”)*(E4:E13>80))
说明：
SUMPRODUCT函数(D4:D13=”女”)表示在D4:D13区域查询性别为女的数据，(E4:E13>80)表示在E4:E13查询成绩大于80的数据，然后将两则数据进行相乘，从而统计80分以上女同学的人数。
六、SUMPRODUCT函数第6种用法：
不间断排名
目的：
根据两个班级学生成绩进行排名
公式：
=SUMPRODUCT(($E$4:$E$13>E4)/COUNTIF($E$4:$E$13,$E$4:$E$13))+1
说明：
对于排名可能大家都会想到使用RANK函数来进行计算排名，但是RANK是国际的一种排名方式，当遇到相同名次时，下一个名次数字顺序就会间断，例如案例中有三位学生都是第5名，顺延下一位学生的名次就变成了第8名了，此时就间断了6和7名的名次，解决不间断名次（中国式排名方式）需要使用SUMPRODUCT函数。
SUMPRODUCT函数($E$4:$E$13>E4)表示在E4:E13成绩区域列是否大于E4成绩,按F9则返回一组数组{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}，这里的TRUE表示的是1，FALSE表示的是0。而COUNTIF($E$4:$E$13,$E$4:$E$13)表示数组中的元素就是对应成绩重复出现的次数，按F9可以看出{1;1;1;3;3;3;1;1;1;1}，这里的3表示的是成绩78分出现3次，1表示没有重复的分数，也就是各分数出现1次。然后将两组数组进行相除，再通过SUMPRODUCT函数进行求和得到数字5，后面再加上1表示的是当前数字的本身排名情况。
如果对以上说明不理解，可以通过这种公式套路进行理解
套路
：
=SUMPRODUCT((条件区域>条件）/COUNTIF（要进行排名的区域))+1
七、SUMPRODUCT函数第7种用法：
按条件排名
目的：
根据两个班级学生成绩，分别进行按班级排名
公式：
=SUMPRODUCT(($B$4:$B$13=B4)*($E$4:$E$13>E4))+1
说明：
SUMPRODUCT函数($B$4:$B$13=B4)表示在B4:B13班级区域列判断是否等于B4,按F9则返回一组数组{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE}，($E$4:$E$13>E4)表示在E4:E13成绩区域列是否大于E4，按F9则返回一组数组{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}。这里的TRUE表示的是1，FALSE表示的是0，通过SUMPRODUCT函数将两组数组乘积求和汇总，再加上1本身成绩排名，从而计算出各班级学生排名情况。
如果对以上说明不理解，可以通过这种公式套路进行理解
套路
：
=SUMPRODUCT((条件区域1=条件1）*(要进行排名的区域))+1
八、SUMPRODUCT函数第8种用法：
模糊条件求和
目的：
根据每个商品的数量和单价，计算笔销量总和
公式：
=SUMPRODUCT(ISNUMBER(FIND(“笔”,B4:B12))*C4:C12*D4:D12)
说明：
由于SUMPRODUCT函数的参数中不支持使用通配符，所以在计算带有关键字的问题，在这里需要截取ISNUMBER解决关键字的问题，首先使用FIND函数在B4:B12数据区域中查找关键字“笔”，如果包含关键字“笔”，就返回表示关键字位置的数值，否则就返回错误值。然后使用ISNUMBER函数判断FIND函数的数组结果是不是数值，如果是数值，说明是包含关键字的，如果不是数值，那就是不包含关键字了。ISNUMBER最终得到由逻辑值TRUE或FALSE构成的内存数组，然后再与后面的区域C4:C12和区域D4:D12依次相乘，最后由SUMPRODUCT返回乘积之和
九、SUMPRODUCT函数第9种用法：
忽略文本求和
目的：
统计所有商品的总数量
公式：
=SUMPRODUCT(C4:C12)
说明：
公式SUMPRODUCT(C4:C12)直接对数量列中包含数字进行求和，忽略文本。当然在这里也可以直接用SUM函数进行汇总。
十、SUMPRODUCT函数第10种用法：
数值和文本混合求和
目的：
根据每个商品的数量和单价，计算笔销量总和
公式：
=SUMPRODUCT(–SUBSTITUTE(D4:D12,”元”,””)*C4:C12)
说明：
由于单价列包含有单位“元”，此时先使用SUBSTITUTE函数将单位元进行替换掉，也就是–SUBSTITUTE(D4:D12,”元”,””)，这里两个减号–，表示的是负负得正，也就是强制地把文本型的数字转换成数值型的，最后通过SUMPRODUCT函数将数量列和单价列的数据进行乘积求和。
作者：Excel办公小课堂
今天分享的内容，建议收藏起来以备不时之需！
关注Excel办公小课堂
解锁办公技能，
告别无效加班
看完点赞，月入过万~