编按:函数课堂第——SUMIF详解。SUMIF拿着求和的工资还干着查找的活,教程让你详细了解SUMIF函数的经典用法和兼职用法。

一、SUMIF的作用及参数

SUMIF对符合某个条件的数据求和。先逐一判断条件区域中的值是否符合条件,然后仅对满足条件者所对应的求和区域中的若干单元格进行求和。

函数语法:

=SUMIF(条件区域,条件,求和区域)

◎条件区域:要进行条件判断的区域,只支持引用。

◎条件:形式灵活,可以为文本、数字、表达式、单元格引用、数组,或函数公式。

◎求和区域:行列数应与第一参数相同;若省略,则表示求和区域等于条件区域。

打开网易新闻 查看更多图片

整个函数的重点在于第二参数。

由于SUMIF函数前两个参数与COUNTIF函数的参数要求完全一致,所以下面侧重用小例子展示其应用。

二、SUMIF函数第二参数条件的常规表达

1.借用逻辑运算符“>”、“<”、“>=”、“<=”、“<>”表达条件

用下图所示。

图中求前三名业绩总和,先用LARGE函数来提取第三名的业绩,前三名的业绩则应该是大于等于第三名,所以写做">="&LARGE(D:D,3)

2.借助通配符*或?进行模糊匹配求和

求李姓员工的业绩总和,可以输入公=SUMIF(A2:A14,"李*",D2:D14),然后回车。

打开网易新闻 查看更多图片

求姓名为两个字的员工业绩总和,可以输入公式:=SUMIF(A2:A14,"??",D2:D14)

三、第二参数条件可以用数组完成或关系的条件求和

譬如需要得到销售一部和销售二部的业绩总和,我们可以理解为求部门等于“销售一部”或者“销售二部”的业绩和。

直接输入公式=SUM(SUMIF(B2:B14,{"销售一部","销售二部"},D2:D14))即可。

这里的{"销售一部","销售二部"}是一个数组,作为SUMIF函数的第二参数,会返回两个结果,最后再用SUM函数来求和即可。

四、SUMIF函数部分经典应用案例

1.单条件多区域求和

当条件区域跟求和区域由一列变成两列,那又该如何对销售一部的业绩进行求和呢?

用两个SUMIF公式相加?如果区域很多的话,写起来就很复杂。

打开网易新闻 查看更多图片

其实SUMIF函数可以直接进行多个数据区域的计算,关键就是第一参数和第三参数是一一对应的。

2.多表格汇总数据

如何对1月、2月、3月各部门的业绩进行求和汇总呢?已知这三个月的数据明细分别位于3张Excel表中,且各表标题字段一样,即A列为部门,C列为业绩。

在B2输入公式=SUM(SUMIF(INDIRECT(ROW($1:$3)&"月!A:A"),A2,INDIRECT(ROW($1:$3)&"月!C:C")))

此公式虽长,但很好理解。先用ROW($1:$3)&"月!A:A"生成3个月的工作表A列的引用地址;外面嵌套一个INDIRECT函数将ROW函数生成的字符串转换成引用;再用SUMIF函数将各个表中销售一部的业绩分别进行求和,最后再用SUM函数进行求和。

记住,数组公式,低版本需要按ctrl+shift+enter组合键结束。(如果用SUMPRODUCT函数取代SUM的话,就可以不用按Ctrl+Shift+Enter。)

3.忽略错误值求和

譬如求下方业绩总和,直接用SUM函数的话,因为D列存在错误值肯定无法求和。这时可以人为增加一个条件,使用SUMIF求和:

9E307表示9乘以10的307次方,是EXCEL可以计算的最大值。条件使用"<9E307"的意思是比9e307小的数值都参与计算,错误值忽略不计。

当然此处都是正数,条件也可以写成">0"。

4.SUMIF的兼职——查找数值

SUMIF函数可以用于查找,并且很多时候比VLOOKUP还有效。

打开网易新闻 查看更多图片

欢迎跟着部落窝函数课堂学函数。

做Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择!

学习交流请加微信hclhclsc进群领取资料

全面解读SUMIF函数的查找匹配功能

函数课堂第十一课:COUNTIF用法详解

SUMPRODUCT函数无所不能

金字塔图表

版权申明:

本文作者逍遥;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。