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

昨天，在公众号直播间有同学建议做sumproduct函数总结。兰色就把最近几年和sumproduct的教程翻了一遍，看完自已都震惊了，原来sumproduct函数这么强大。

 基本用法

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

=SUMPRODUCT(B2:B7,C2:C7)

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

 扩展用法

原本它只是一个很简单的一个分组乘积后再求和的函数，后被高手挖掘出了很多扩展用法。不但可以求和，还可以计数、查找、计算排名。这简直是抢Coutifs、Sumifs、Lookup等函数的饭碗啊。

• 含文本数字的求和
  

• 多条件求和
  

• 不重复值计数

• 按条件整行求和
  

• 隔列求和
  

• 多表求和
  

• 多条件查找
  

• 交叉查找
  

• 中国式排名
  

1、含文本数字的求和

【例】对B列的数字求和，其中含有文本型数字

 =SUMPRODUCT(B2:B10*1)

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

2、多条件求和

【例】如下图所示，根据A11的产和b11的类别统计总的数量

=SUMPRODUCT((A2:A7=A11)*(B2:B7=B11)*C2:C7

3、不重复的值计数

【例】下图所示的客户消费明细表中，要求计算客户的总人数。

=SUMPRODUCT((1/COUNTIF(B2:B10,B2:B10)))

4、按条件整行求和

【例】根据A11中的姓名，计算其1~6月份合计。

=SUMPRODUCT((A2:A6=A11)*B2:G6)

5、隔列求和

如果没有标题，可以用Sumproduct函数公式：

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

6、多表求和

【例】如下面动图所示，要求计算本年所有月份的各个部门的工资合计数。

汇总表B2单元格公式：

=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$3)&'月!A:A'),A2,INDIRECT(ROW($1:$3)&'月!C:C')))

7、多条件查找

如果要返回数字且无重复，也可以用Sumproudct函数。

【例】根据E列姓名和月份，从左表中查找其数量

=SUMPRODUCT((A2:A31=E2)*(B2:B31=F2)*C2:C31)

8、交叉查找

【例】根据姓名和月份，要求从下图上表中查找对应的销量

C14公式

=SUMPRODUCT((A2:A11=A14)*(B1:G1=B14)*B2:G11)

9、中国式排名

【例】如下图所示，在C列计算B列的销量的排名，

=SUMPRODUCT(($E$3:$E$13>=E3)*(1/COUNTIF(E$3:E$13,E$3:E$13)))

兰色说：由于时间关系，兰色总结的也只是sumproudct函数用法的一部分，可见这个函数远不是我们平时想像的那么简单。

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

工作中最常用的Excel函数公式，全印在一张超大的鼠标垫上(送40集配套视频)，点我查看详情