Excel函数之Sumproduct，6个经典用法，你真的都了解吗？

Sumproduct，可以将其分为两个部分，Sum和Product，Sum是求和，Product是乘积，综合到一起，就是对各个数据参数先乘积，再返回乘积之和。

一、Sumproduct——常规用法

功能：返回相应的数据或区域乘积的和。

语法结构：=Sumproduct(数据1,[数据2],[数据3]……)。

目的：计算各类商品的总采购额。

方法：

在目标单元格中输入公式：=SUMPRODUCT(E3:E12,F3:F12)。

解读：

Sumproduct函数的功能是返回相应的数据或区域乘积的和，公式=SUMPRODUCT(E3:E12,F3:F12)中，数据区域有E3:E12和F3:F12两个，所以计算过程为：E3*F3+E4*F4+E5*F5+……+E11*F11+E12*F12，对应数据元素先乘积，后求和，得到最终的总采购额。

二、Sumproduct——单条件求和

目的：按【部门】统计总采购额。

方法：

在目标单元格中输入公式：=SUMPRODUCT((D3:D12=I3)*E3:E12*F3:F12)。

解读：

公式=SUMPRODUCT((D3:D12=I3)*E3:E12*F3:F12)中，有三个数据区域，分别为D3:D12=I3、E3:E12、F3:F12，当D3:D12区域中的值和I3单元格中的值相等时，返回1，否则返回0，然后三个数据对应元素先乘积，再契合，从而计算得到相应部门的总采购额。

三、Sumproduct——多条件求和

目的：相应【部门】采购相应【商品】的总采购额。

方法：

在目标单元格中输入公式：=SUMPRODUCT((B3:B12=J3)*(D3:D12=I3)*E3:E12*F3:F12)。

解读：

计算过程同“单条件求和”类似，只是增加了一个判断条件而已。

四、Sumproduct——单条件计数

目的：统计商品总采购【数量】。

方法：

在目标单元格中输入公式：=SUMPRODUCT((B3:B12=I3)*(E3:E12))。

解读：

公式=SUMPRODUCT((B3:B12=I3)*(E3:E12))中，有两个数据区域，一个为B3:B12=I3，另一个为E3:E12，第一个数据区域中当B3:B12=I3这个条件成立时，返回1，否则返回0，所以第一个数据区域对应的元素值为，与第二个数据元素的值先乘积，再求和，从而得到计数的结果。

五、Sumproduct——多条件计数

目的：对相应【采购部门】采购的【商品】进行计数统计。

方法：

在目标单元格中输入公式：=SUMPRODUCT((D3:D12=I3)*(B3:B12=J3)*E3:E12)。

解读：

具体的计算过程请参阅“单条件计数”的解读部门。

六、Sumproduct——中国式排名

目的：对单个【商品】的【采购额】进行排序。

方法：

在目标单元格中国输入公式：=SUMPRODUCT(($G$3:$G$12>G3)/COUNTIF($G$3:$G$12,$G$3:$G$12))+1。

解读：

1、从结果中可以看出，公式=SUMPRODUCT(($G$3:$G$12>G3)/COUNTIF($G$3:$G$12,$G$3:$G$12))+1的排序结果降序，如果要按升序排序，则只需将公式中的$G$3:$G$12>G7更改为>G3>$G$3:$G$12即可。

2、公式整体上可以看成3部分组成，一部分为：$G$3:$G$12>G3，另一部分为：COUNTIF($G$3:$G$12,$G$3:$G$12），最后一部分为附加值+1。计算时，首先判断$G$3:$G$12>G3是否成立，以H3单元格为例，则返回，而COUNTIF($G$3:$G$12,$G$3:$G$12）统计的是$G$3:$G$12区域中的每个值在$G$3:$G$12区域中的出现次数，即返回，对应的数据元素先进行除法（/）计算，返回,并返回给Sumproduct作为参数，而Sumpoduct中，当只有一个数据区域时，对该区域的值进行求和运算，即0+1+1+1+1+1+1+0+1+0，最后等于7，加上辅助值1，得到想要的结果8。