成为VIP
今天给大家分享一个很常用、很实用、很强大、也很有意思的函数，Sumproduct。众所周知，条件计数和求和是数据的统计和分析中经常要用到的2种技巧，而Sumproduct函数不但集合了条件计数、求和功能，还可以用于复杂情景下的排名处理等情景，所以有人说，精通Sumproduct函数，就可以打下Excel的半壁江山。
一、功能及语法结构。
功能：返回相应的数组或区域乘积的和。
语法结构：=Sumproduct(数组1,[数组2],[数组3]……)。
解读：
1、我们可以将函数名称Sumproduct分为2部分，Sum和Product，Sum是求和的意思，Product是乘积的意思qsort函数，参数之间先乘积再求和。
2、从语法结构可以看出，Sumproduct函数本身默认执行数组运算。
3、Sumproduct函数会将参数中非数值类型的数组元素作为0处理。
4、各参数的维度必须相同，否则会返回错误值。
二、应用案例。
1、计算总销售额。
方法：
在目标单元格中输入公式：=SUMPRODUCT(D3:D12,E3:E12)。
解读：
1、如上图，要计算商品的总销售额，一般思路为首先计算出每个商品的销售额，然后求和。也就是需要2步来完成。
2、而Sumproduct函数将上述的两个步骤合二为一，公式=SUMPRODUCT(D3:D12,E3:E12)的计算过程为：D3*E3+D4*E4……D12*E12，也就是每个数组对应的元素相乘，再求和。
3、部分亲可能也遇到过将公式写成：=SUMPRODUCT(D3:D12*E3:E12)的情况，同样能够得到正确的结果。Why？我们接着往下学习。
2、计算总销售额。
在计算之前，我们先将数据表稍作改动，如下图：
从图中可以看出，商品“打印机”的销量“待统计”，此时用公式：=SUMPRODUCT(D3:D12*E3:E12)来计算，结果如下图：
从结果中可以看出，返回了错误值，Why？
分析数据源，不难发现，原因在于“打印机”的“销量”为“待统计”，为文本值，而文本值是无法直接参与数据运算的，所以D5*E5返回错误值，进而导致整个公式返回错误值。但如果将公式修改为：=SUMPRODUCT(D3:D12,E3:E12)，结果如下图：
结果没有任何问题，是正确的。
原因在于Sumproduct函数会将参数中非数值类型的数组元素作为0处理。
综上所述，敲一下黑板：
当Sumproduct函数的参数为数值类型时，公式=Sumproduct(数组1,[数组2],[数组3]……)或=Sumproduct(数组1*[数组2]*[数组3]……)均可用于计算；而参数中含有非数值类型时，只能用=Sumproduct(数组1,[数组2],[数组3]……)进行计算。
3、参数的维度必须相同。
目的：计算商品的总销售额。
在目标单元格中输入公式：=SUMPRODUCT(D3:D12,E3:E10)，结果如下图：
结果返回错误值，从公式中不难发现qsort函数，公式中的两个元素的维度不同，E3:E10明显比D3:D12少了2个元素，D3和E3结对，D4和E4结对……D11以及D12和谁结对呢？一个萝卜一个坑，D11和D12有萝卜没坑，所以就返回了错误值。
4、单条件计数。
如下图：
现在要计算员工中相应“学历”的人数，如果用Countif函数计算，公式为：=COUNTIF(F3:F24,J3)，但如果用Sumproduct去计算，怎么操作呢？
从示例图中可以看出，公式为：=SUMPRODUCT((F3:F24=J3)*1)；先判断F3:F24=J3是否成立，如果成立，返回TRUE，否则返回FALSE，由此建立一个由逻辑值组成的数组，前文中已经讲过，Sumproduct函数会将非数值型的数组元素作为0处理，逻辑值自然属于非数值型的数组元素，为了避免Sumproduct函数将逻辑值视为0，造成统计错误，所以乘以辅助值1，把逻辑值转换为数值类型的值，最后统计求和，得到计数结果。
5、单条件求和。
目的：统计相应“学历”人员的总“月薪”。
单条件求和，应该是Sumif函数的本职工作，公式为：=SUMIF(F3:F24,J3,G3:G24)，其实除了用Sumif函数之外，用Sumproduct函数也可以实现。
方法：
在目标单元格中输入公式：=SUMPRODUCT((F3:F24=J3)*G3:G24)。
解读：
1、首先判断F3:F24=J3是否成立，建立一个由逻辑值组成的数组，然后和G3:G24元素中相应的值乘积，再求和。
2、如果此处将公式更改为：=SUMPRODUCT(F3:F24=J3,G3:G24)，能否得到想要的结果呢？大家可以思考一下，将自己的意见发表到留言区或者私信和小编讨论交流哦！
6、多条件计数、求和。
目的：按“性别”统计相应“学历”的人数和总“月薪”。
按“性别”统计相应“学历”的人数，这是一个多条件计数的问题，如果用Countifs函数去计算，公式为：=COUNTIFS(D3:D24,J3,F3:F24,K3)，参数相对来说较多，有点儿简单问题复杂化的感觉，所以除了用Countifs函数外，还可以用Sumproduct函数。
公式为：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3))。
除了计数之外，还要按“性别”统计相应“学历”的总“月薪”，就属于求和的问题，而且是多条件求和，公式为：=SUMIFS(G3:G24,D3:D24,J3,F3:F24,K3)；也有点儿简单问题复杂化，如果用Sumproduct函数来实现：
公式为：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3)*G3:G24)。
也可以是：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3),G3:G24)，Why？留言区或私信告诉小编哦！
应用拓展：
如果老板要知道“男”同志或“女”同志“大本”、“大专”、“职高”的总人数和总“月薪”，该如何操作呢？
通过上文的学习，相信大家已经能够解决这个问题，就是将公式进行叠加。
计数公式为：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3))+SUMPRODUCT((D3:D24=J3)*(F3:F24=K4))+SUMPRODUCT((D3:D24=J3)*(F3:F24=K5))。
求和公式为：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3)*G3:G24)+SUMPRODUCT((D3:D24=J3)*(F3:F24=K4)*G3:G24)+SUMPRODUCT((D3:D24=J3)*(F3:F24=K5)*G3:G24)
貌似没有任何问题，但仔细分析，如果有20个学历等次或其他等次，公式要重复20次，手累就不谈了，万一写错，………………是不是很麻烦？看小编是如何处理的？
计数公式为：=SUMPRODUCT((D3:D24=J3)*(F3:F24={“大本”,”大专”,”职高”}))。
求和公式为：=SUMPRODUCT((D3:D24=J3)*(F3:F24={“大本”,”大专”,”职高”})*G3:G24)。
是不是清晰了很多，只需要给大括号中添加条件即可。
解读：
1、多条件计数时，能够将公式：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3))更改为：=SUMPRODUCT((D3:D24=J3),(F3:F24=K3))？
2、多条件求和时，为什么=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3)*G3:G24)和=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3),G3:G24)均可使用？
7、排序。
目的：对相应“学历”下的“月薪”进行排序。
方法：
在目标单元格中输入公式：=SUMPRODUCT((F$3:F$24=F3)*(G$3:G$24>G3))+1。
思考
1、公式有几个参数？
2、为什么+1，而不是直接写成：=SUMPRODUCT((F$3:F$24=F3)*(G$3:G$24>G3))？欢迎在留言区留言讨论哦！
结束语：
说了很多，都是关于Sumproduct的实际应用，可以计数、求和，还不止，计数中还可以实现单条件计数、多条件计数；求和中可以实现单条件求和、多条件求和；除此之外，还可以排序，当然了，排序不仅仅是对数值的排序（怎么排？？？），还可以附加多个条件进行排序，覆盖了Sum、Sumif、Sumifs、Countif、Countifs、Rank等函数的功能，难道还不够强大？
限时特惠：本站每日持续更新海量展厅资源，一年会员只需29.9元，全站资源免费下载
站长微信：zhanting688
