⼀帖读通函数中的神器：SUMPRODUCT SUMPRODUCT是函数⾥⼏⼤神器之⼀，兼具条件求和及计数两⼤功能，适⽤性极强，堪称上得厅堂下得厨房打得过⼩三⽃得过流氓，是当之⽆愧的函数劳模。
众所周知，条件求和及计数，是表哥表妹们经常碰到的问题，于是SUMPRODUCT便不可不学了。
SUMPRODUCT的官⽅“简历”是这个样⼦：
SUMPRODUCT（array1,array2,array3, ...）
对应的元素相乘，并返回乘积之和。——SUM是求和的意思，PRODUCT是相即在给定的⼏组数组（array）中，将数组间对应
乘的意思，相乘之后再求和，你看，SUMPRODUCT真是⼈如其名了。
SUMPRODUCT函数有以下特点：
其⼀：本⾝⽀持数组间运算。
其⼆：它会将⾮数值型的数组元素作为0处理。
其三：数组参数必须有相同的尺⼨，否则返回错误值。
countifs函数怎么输入条件
看完了SUMPRODUCT的“简历”，很多朋友想必是雾⾥看花，仅仅对它有个模糊的认知，它的这些特点是啥意思？它到底能够胜任什么样⼦的⼯作？其实并不了然——⾄少我当初是不了然的。
下⾯，我们通过⼏个⽰例，对它来做进⼀步的了解。
下⾯这个表，C列是商品单价，D列是销售数量，现在需要在C9单元格计算销售总额。
C9输⼊公式：
=SUMPRODUCT(C3:C7, D3:D7)
回车后，得出结果￥11620.60
对应相乘。
这便是⼀个简单的SUMPRODUCT函数，它的运算过程是：C3:C7和D3:D7两个区域数组内的元素对应
展⽰开来：C3*D3,C4*D4,C5*D5……直⾄C7*D7。
等于先将每个商品的销售⾦额计算出来，最后汇总求和。
由于SUMPRODUCT函数第⼀个特点，本⾝是⽀持数组间运算的，所以虽然该公式需执⾏多项运算，但并不需要按数组三键Ctrl Shift Enter结束。
有的朋友说啦，我公式也可以写成这样：
=SUMPRODUCT(C3:C7*D3:D7)
或者写成这样:
=SUM(C3:C7*D3:D7)，按<Ctrl Shift Enter>组合键结束公式输⼊，也没有问题哇。
那么这三个公式之间有什么区别呢？
⾸先，SUMPRODUCT不需要数组三键结束公式输⼊，⽽此处的SUM函数是需要的。
其次，就要说到SUMPRODUCT函数另⼀个⾮常重要的特点了。
我们将上⾯的表稍做改动，将“钢笔”的销售数量更改为：暂未统计。同样需要在C9单元格计算销售总额。
这时候，如果使⽤公式：
=SUMPRODUCT(C3:C7*D3:D7)
C9单元格会返回错误值#VALUE!。
{=SUM(C3:C7*D3:D7)}，也同样返回错误值。
原因在于，D4单元格的值“暂未统计”为⽂本，⽂本是⽆法参与数学运算的，于是C4*D4返回错误值#VALUE!，进⽽造成整个公式的结果返回错误值。
⽽使⽤公式：
=SUMPRODUCT(C3:C7,D3:D7)，将得出正常结果￥11385.60。
这便是SUMPRODUCT函数的第⼆个特点：将⾮数值型的数组元素作为0处理。
以该⽰例来说，D4单元格的值“暂未统计”为⽂本，并⾮数值，SUMPRODUCT将其主动视为零，于是C4*D4，结果亦为零，其余数组元素照常计算，得出￥11385.60的结果。
需要特别说明的是，SUMPRODUCT将⾮数值型的数组元素作为0处理，所谓的⾮数值型数组元素，包含逻辑值、⽂本，但并如果数组元素中包含错误值，该公式亦返回错误值，⽐如该⽰例的第⼀条公式。
不包含错误值，如果数组元素中包含错误值，该公式亦返回错误值，⽐如该⽰例的第⼀条公式。
说完了SUMPRODUCT函数的两个特点，我们就再来聊聊它的第三个特点：数组参数必须有相同的尺⼨，否则返回错误
数组参数必须有相同的尺⼨，否则返回错误值。
我们依然⽤上述图⽚的例题为例，继续计算商品的销售总额。如果我们在C9输⼊公式：
=SUMPRODUCT(C3:C7,D3:D6)
结果会是怎么样的呢？
错误值：#VALUE!
为什么这样？
细⼼的您，肯定注意到了，两个区域数组，C3:C7明显显⽐D3:D6多了⼀个元素，C3和D3结对⼦，C4和D4结对⼦……那么C7和谁结对⼦呢？⼥⼈们都嫁了，结果剩下⼀个光棍，这⽇⼦没法过了！⼀个萝⼘⼀个坑，只有萝⼘没有坑，这不是要萝⼘死吗？
—
—于是SUMPRODUCT就不⾼兴了，它给你⼀个错误值#VALUE!，明确告诉你，⽇⼦不能这么过。
数组参数必须有相同的尺⼨，否则返回错误值。
这就是SUMPRODUCT函数的第三个特点：数组参数必须有相同的尺⼨，否则返回错误值
下⾯是⼀道练习题，你看看，能⽤SUMPRODUCT函数做出来吗？
第⼆部分
假设上⾯这张图，是某个公司⼯资发放的部分记录表（数据纯属虚拟，如有雷同，那是穿越咯）。A列是⼯资发放的时间，B 列是员⼯所属的部门，C列是员⼯姓名，D列是相关员⼯领取的⼯资⾦额。——那么，问题来了：
⼀，员⼯西门庆领取了⼏次⼯资？
这是⼀个单条件计数的问题，通常我们使⽤COUNTIF函数，但如果使⽤SUMPRODUCT函数，⼀般写成这样：
=SUMPRODUCT((C2:C13="西门庆")*1)
先判断C2:C13的值是否等于”西门庆”，相等则返回TRUE，不等则返回FALSE，由此建⽴⼀个有逻辑值构成的内存数组。
逻辑值⾃然是属于⾮数值型的数第⼀部分咱们说过，SUMPRODUCT有⼀个特性，它会将⾮数值型的数组元素作为0处理，逻辑值⾃然是属于⾮数值型的数组元素，为了避免SUMPRODUCT函数把逻辑值视为0，造成统计错误，我们使⽤*1的⽅式，把逻辑值转化为数值，TRUE转组元素
化为1，FALSE转化为0，最后统计求和。
⼆，员⼯西门庆累计领取了多少⼯资？
这是⼀个单条件求和的问题，通常我们使⽤SUMIF函数，如果使⽤SUMPRODUCT函数，我们可以写成这样：
=SUMPRODUCT((C2:C13="西门庆")*D2:D13)。
依然⾸先判断C2:C13的值是否等于”西门庆”，得到逻辑值FALSE或TRUE，再和D2:D13的值对应相乘，最后统计求和得出结果。
看完了上⾯两个问题，有些表亲⼼⾥就嘀咕了，貌似SUMPRODUCT能⼲的事，SUMIF和COUNTIF也能做到，⽽且做的更好，那么还要SUMPRODUCT⼲啥？
乡亲们呐，话不能这么说，SUMPRODUCT可是上得厅堂下得厨房，对⼯作环境不挑不拣，它对参数类型没有啥特别要
求，COUNTIF和SUMIF就不同咧，他俩要求个别参数，必须是区域（Range型），不⽀持数组，⽐如下⾯这两个问
题，COUNTIF和SUMIF就要绕了。
三，六⽉份财务部发放了⼏次⼯资？累计⼜发放了多少？
多条件计数的问题，第⼀个条件，发放⼯资的时间必须是六⽉份；第⼆个条件，发六⽉份财务部发放了⼏次⼯资，这是⼀个多条件计数的问题
放⼯资的部门必须是财务部。
如果使⽤多条件计数函数COUNTIFS，判断发放⼯资的时间是否属于六⽉份，会简单问题复杂化。⽽使⽤SUMPRODUCT函数，咱们可以简单利索的写成这样：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部"))
多条件求和问题。如果使⽤SUMIFS函数，判断发放⼯统计六⽉份财务部发放了多少⼯资？表亲们都晓得，这是⼀个常见的多条件求和
资的时间是否属于六⽉份，和COUNTIFS类似，也会简单问题复杂化。SUMPRODUCT跃然⽽⾄：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部"),D2:D13)
或者：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")*D2:D13)
打个响指，关于这两个形式的SUMPRODUCT函数的区别，咱们第⼀部分也有详细说明，亲，你还记得吗？——啥？不记得？嘿！赶紧回头看⼀下吧。
上⾯这个公式可以说是SUMPRODUCT多条件求和的典型⽤法啦，可以归纳为:
=SUMPRODUCT((条件⼀)*(条件⼆)……，求和区域)
四，六⽉份财务部和市场部合计发放了多少⼯资？
通过上⾯第三个问题，表亲们已经晓得如何计算六⽉份财务部发了多少⼯资，那么六⽉份财务部和市
场部合计发了多少⼯资，⼜当怎么计算呢？
我们经常见有些性格朴素的表亲们把公式写成这样：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")*D2:D13) SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="市场部")*D2:D13)
这些表亲们估计⼼想，不就是计算两个部门吗？甭说两个，⼆⼗个咱也能算，⼀个加⼀个，⼀直加到⼆⼗个，世上⽆难事，只怕有⼼⼈嘛，⼀砖加⼀砖，长城咱也能垒成喽，⼀泡加⼀泡，黄河咱也能……
公式写的那么长，先不谈计算速度，⾸先它累⼿啊，其次万⼀写错了，⼜要修改，那也是⿇烦他妈哭⿇烦——⿇烦死了。其实我们可以写成这样：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13={"财务部","市场部"})*D2:D13)
函数在条件计数和求和⽅⾯的⽤法，最后，咱们再来看⼀个它在排名上的使⽤⽅法。
五，认识了SUMPRODUCT函数在条件计数和求和⽅⾯的⽤法，最后，咱们再来看⼀个它在排名上的使⽤⽅法
如上图，某个⽉某个公司某些⼈领了某些⼯资，然后呢，他们想看看⾃⼰的⼯资，在部门内的排名情况，⽐如说财务部的鲁智深、市场部的卢俊义，都是⽼员⼯了，⾮常想知道⾃个⼯资在各⾃部门排⼏号。
当然啦，不排不知道，⼀排就傻掉。
SUMPRODUCT是这么解决这个问题的，D2输⼊公式向下复制：
=SUMPRODUCT(($A$2:$A$9=A2)*(C2<$C$2:$C$9)) 1
（思考，为什么公式的最后 1，⽽不是直接写成=SUMPRODUCT(($A$2:$A$9=A2)*(C2<=$C$2:$C$9))？
第三部分
唠唠叨叨说了这么多，眼睛都说酸⿇了，是到了该结束的时候啦。
说⼀下第⼀部分练习题的答案：
最后，请思考以下两个问题：
第⼀个问题：
=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")*D2:D13)，这个公式⾥，SUMPRODUCT函数有⼏个参数？=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部")，D2:D13)，这个公式⾥，SUMPRODUCT函数⼜有⼏个参数？
第⼆个问题：
SUMPRODUCT为什么⽐SUMIF/COUNTIF计算速度慢？
我是星光，等你看见，我是看见星光》》》》》》》

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。