可以求和的函数很多,但是SUMPRODUCT是绕不过去的那个,它不仅可以条件计数还可以求和,且自带数组计算,不用三键,对新手非常友好,今天我们就来聊聊SUMPRODUCT这个函数!

我们还是通过一系列案例,在案例中讲解我们相关知识点!

案例1 | 根据数量和单价求金额

=SUMPRODUCT(E2:E14,F2:F14)

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

正常应该考虑添加一列计算出金额,然后再求和,SUMPRODUCT可以完成对应的单元格相乘最后相加,当然我们也可以写成一下的样式,可能更好的理解

=SUMPRODUCT(E2:E14*F2:F14)

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

结果虽然一样,但是下面的大家看上去更直观,SUMPRODUCT 正确的理解是 PRODUCT(乘)+SUM(求和),先乘后加,但是两种写法也是有区别的,这个差别也就是SUMPRODUCT其中一个特性,我们来看一下

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

如果我们价格中有部分价格还没确定,就好像考试成绩写着缺考一样,这个时候,我们使用乘法的格式是得不到想要的结果的,错误值

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

但是两个数组分开写是可以的,其中的价格 待确定这项会被忽略,

这个非常符合我们实际情况!

这种我们就来说一下SUMPRODUCT的特性:SUMPRODUCT 将非数值条目视为零,这种的非数值包括逻辑值(TRUE和FALSE),但是值得注意的是他无法忽略错误值,比如下面这样就会报错,想要得到正确的结果,我们需要其他函数辅助比如IFERROR

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

遇到错误值正确的处理方式如下:

=SUMPRODUCT(IFERROR(E2:E14,),F2:F14)

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

IFERROR把错误值转换成了0,这样我们就可以得到正确的结果!

其实SUMPRODUCT还有一个特性,就是要求数组或者区域大小一致,这个好理解,就是尺寸一致,一一对应起来,不能单价3行,数量5行!

案例1,主要带大家认识一下SUMPRODUCT,了解一下数组逗号写好的可以忽略非数值的特性!

案例2 | 单条件求和-东北区域的金额合计

=SUMPRODUCT((B2:B14="东北区域")*E2:E14*F2:F14)

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

一样如果单价中有文本,需要写成如下公式:

=SUMPRODUCT((B2:B14="东北区域")*1,E2:E14*F2:F14)

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

肯定有人会问为什么乘以1,所以我们解释一下!由于SUMPRODUT忽略非数值,所以逻辑值也会被忽略,B2:B14=”东北区域” 结果是一组逻辑是TRUE和FALSE,

想要计算就需要转成数字,逻辑值记过加减乘除四则运算就会变成数值,其中TRUE*1=1,FALSE*1=0,这样上面就好理解,不是东北区域的也就是都乘以了0,最后结果都是0,东北区域的就乘以1,最后相加OK!

案例3 | 条件计数 – 小易共销售几次

=SUMPRODUCT((C2:C14="小易")*1)

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

原理我们在案例2中说过,逻辑值乘以1,TRUE*1=1,FALSE*1=0,最后相加,也就是条件计数,当然你也可以使用COUTNIF(S)也是一样!

=COUNTIF(C2:C14,"小易")

案例4 | 多条件求和-华南区域李四的销售金额合计

=SUMPRODUCT((B2:B14="华南区域")*(C2:C14="李四")*E2:E14*F2:F14)

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

连乘即可,更多条件同理,就不再多啰嗦了!

案例4 | 或者关系-张三和李四的销售数量合计

=SUMPRODUCT((C2:C14="张三")+(C2:C14="李四"),F2:F14)

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

这里更多的是Excel逻辑值计算的问题,而不是SUMPRODUCT的知识点!由于同一个单元格不可能既是张三又是李四,(C2:C14=”张三”)+(C2:C14=”李四”) 结果只会是1 或者0,可能出现2,这样最后和数量相乘即可

当然我们也可以使用常量数组简化写法!

=SUMPRODUCT((C2:C14={"张三","李四"})*F2:F14)

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

案例5 | 字段处理后求和-求7月销售数量合计

=SUMPRODUCT((MONTH(A2:A14)=7)*(F2:F14))

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

这个案例主要是说明,我们可以对某一列或者几列加工处理后作为SUMPRODCUT的条件,比如还可以字符截取,姓赵的销售数量合计等等都是可以的,这样可以让我们灵活!

这里顺便带一下SUMIFS的思路

=SUMIFS(F:F,A:A,">=2019-7-1",A:A,"<="&EOMONTH(--"2019-7-1",0))

利用EOMONTH找到指定日期的当月最后一天的日期~!

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

OK,SUMPRODUCT今天我们就说这么多,你会用了吗 ?快去试试吧!

小结

Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网Sumproduct函数的使用方法及实例(SUMPRODUCT是什么意思)-风君歌曲歌词大全网

1、忽略非数值,这点算是SUMPRODUCT的一个优点,使用其他函数可能还要使用容错处理

2、SUMPRODCUT虽然好用,但是相对于SUMIF函数而已是一个“慢”函数,当逻辑复杂和数据较多时卡的情况会非常明显,实战中一般会考虑SUMI(S)来替换,尤其复杂量大的情况下!

3、不要使用整列或者整行作为SUMPRODCUT的条件区域,否则一个公式会计算1048576次

4、SUMPRODCUT自带数组计算,案例中从来都没有出现过{}三键录入的情况,这点对于新手是非常的友好!