吊打Sumifs，气晕lookup，它是怎么把自已做成Excel万能函数的？

有一个函数，它隶属Sum家族，却比Sum好用十倍。原本它只是一个很简单的一个分组乘积后再求和的函数，后被高手挖掘出了很多扩展用法。它就是你很熟悉但不一定会用的：

SUMPRODUCT函数

基本用法

Sumproduct函数的本职工作是返回两组数乘积的和，如：

=SUMPRODUCT(B2:B7,C2:C7)

等同于B和C列一一对应求积，然后再用SUM求和。

这个函数特别不守本份，到处插足。不信请向下看：

01.插足单条件计数（抢Countif饭碗）

例：统计奥迪品牌型号个数

=SUMPRODUCT((A2:A5=B8)*1)

注：(A2:A5=B8)返回的是TRUE或FALSE，*1可以转换为1或0

02.插足单条件求和（抢Sumif饭碗）

例：统计A产品的销售合计

=SUMPRODUCT((B3:B9='A')*C3:C9)

03.插足多条件计数（抢Countifs饭碗）

例：计算A产品3月的销售次数

=SUMPRODUCT((B3:B9='A')*(MONTH(A3:A9)=3))

04.插足单条件求和（抢Sumifs饭碗）

例：计算A产品3月的销量

=SUMPRODUCT((B3:B9='A')*(MONTH(A3:A9)=3)*C3:C9)

05.插足多条件查找（抢Lookup饭碗）

如果满足以下两个条件，Sumproduct函数还可以轻松的查找。

• 查找的内容在表中唯一

• 被查找的为数字

例：查找奥迪Q7的库存数最

=SUMPRODUCT((A2:A5=A8)*(B2:B5=B8)*C2:C5)

06.插足数组求和（抢数组公式饭碗）

由于Sumproudct支持数组运算，它抢了数组公式的饭碗。

例：计算不重复个数

=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))

07.插足双向查找（抢index+match饭碗）

如果查找结果是数字，双向查找用它也非常简单

例：根据姓名和月份查销售量

=SUMPRODUCT(($B$3:$B$7=B10)*($C$2:$H$2=C10)*$C$3:$H$7)

如果你认为Sumproduct函数只是简单的抢饭碗，就错了。它还可以完成其他函数很难做到的计算。

08.隔列求和

如下图所示，要求对表格进行隔列求和。

公式:H1

=SUMPRODUCT((MOD(COLUMN(A1:G1)+2,3)=0)*A1:G1)

09.含文本数字的区域求和

含文本数据求和，直接用Sum会出错的。用SUMPRODUCT则可以正确计算。

=SUMPRODUCT(B2:B10*1)

注：*1可以把文本型数字转换成数值型数字

10.长数字条件求和

用Sumif公式出现了错误的结果，明明是404.76，结果是2624.55。

而用Sumproduct则可以计算出正确结果。

=SUMPRODUCT((A2:A16=D2)*B2:B16)

11.多列联合判断

sumifs和countifs只能就指定列设置条件，而sumproduct可以多列联合起来判断。

如下图所示，要求统计”库存数小于标准库存“的产品个数。

=SUMPRODUCT((B2:B7<C2:C7)*1)

12、对内存/常量数组条件求和

由于sumifs和countifs的只能对单元格区域的值判断，所以对常量数组或内存数组只能用sumroduct了。如:

=SUMPRODUCT(({12,3,8,2,20}>10)*1)

兰色说：什么是Excel函数高手，如果你把某个函数用到不再像它，你就是Excel函数高手了。

长按下面二维码图片，点上面”前往图中包含的公众号“然后再点关注，每天可以收到一篇兰色最新写的excel教程。