EXCEL中的sumif函数在什么情况下使用?
我先来说说sumif的语法结构:
SUMIF(range, criteria, [sum_range])
range 必需。用于条件计算的单元格区域。
criteria 必需。用于确定对哪些单元格求和的条件。
sum_range 可选。要求和的实际单元格。如果 sum_range 参数被省略,Excel 会对在 range 参数中指定的单元格(即应用条件的单元格)求和。
下面我将SUMIF在八种情况在的使用演示一下,希望能对大家有所帮助!
1,条件求和 – 精确值求和。
示例中求出类型为“订阅号”的用户数量的总和。
公式:F13=SUMIF($C$5:$C$10,D13,$E$5:$E$10)
2,条件求和 – 范围值求和。
示例中求出大于500的用户数量的总和。
SUMIF省略第三个参数时,会对第一个参数进行求和。
公式:F13=SUMIF($E$5:$E$10,D13)
3,含错误值求和。
示例中求出F列用户数量的总和。其中F列含有错误值。
公式:F11=SUMIF(F5:F10,”>0″)
4,模糊条件求和- 通配符单条件求和。
示例中求出B列中含有“office”的用户数量总和。
* 代表通配符。
公式:E13=SUMIF(B5:B10,D13,E5:E10)
5,多条件模糊求和 – 通配符多条件求和。
示例中求出B列中含有“office” & “buy”的用户数量总和。
* 代表通配符。
公式(嵌套了数组公式):E13=SUM(SUMIF(B5:B10,{“*Office*”,”*buy*”},E5:E10))
Ctrl+Shift+Enter组合键输入数组公式
6,多条件求和。
示例中求出B列中officeGif/Goalbuy/Echat458对应的用户数量总和。
公式(嵌套了数组公式):E13={SUM(SUMIF(B5:B10,D13:D15,E5:E10)}
Ctrl+Shift+Enter组合键输入数组公式
7,根据等级计算总分值。
H5数组公式:{=SUM(SUMIF($J$6:$J$10,C5:G5,$K$6:$K$10))}
8,跨区域提取编号。
公式:C12=SUMIF($C$4:$G$8,B12,$B$4:$F$8)
注意:编号均为数值,才能用SUMIF函数提取
我们在工作中经常会遇到条件求和的情况,这时候,SUMIF函数就可以派上大用场!今天,技巧君就来讲一下SUMIF函数的用法!
【本文示例文件获取方法见文末】
SUMIF函数是什么
功能:对满足条件的单元格的数值求和
语法:SUMIF(条件区域,条件,[求和区])
什么时候用SUMIF
条件求和是什么意思呢?比如,给你一个各地分公司业绩流水账,里面有北京分公司、上海 分公司、广州分公司 等各地分公司子部门的业绩情况,要求出北京分公司的总业绩 ,这种情况就称为条件求和 !
条件求和的“条件”我们可理解为语法中的 “定语”,比如以一个公司的所有员工为 研究对象,要求公司男员工的总业绩,这里的“男”就是一个限定条件,我们的条件区就是“性别”,求求和区域为“业绩”,那么我们的公式为:
SUMIF(性别,“男”,业绩)
SUMIF具体怎么用
例1、基础用法,求人事部总工资
=SUMIF(B1:B10,F3,D1:D10)
例2、省略求和区,求≥5000的总工资
=SUMIF(D1:D10,”>=5000″)
例3、计算除人事部以外的总工资
=SUMIF(B1:B10,”<>”&F11,D1:D10)
例4、计算工资低于平均值的总工资
=SUMIF(D1:D10,”<“&AVERAGE(D1:D10))
例5、计算姓马的员工工资总额
=SUMIF(A1:A10,G7&”*”,D1:D10)
好了,以上就是常见的五种SUMIF函数的用法!欢迎留言评论一起探讨哦!
示例文件获取
点击右上角红色按钮 关注EXCEL精选技巧,然后私信发送【SUMIF函数】即可获取
关注EXCEL精选技巧,每天学习3分钟,坚持一个月,你将大不同!
sumif函数使用情况多种多样,如果你了解这几个函数 IF,SUM,SUMIF,SUMIFS.的原理,情况在复杂在不怕了。
我们通过几张图来理解这几个函数,深刻得了解这几个常用函数
1、先来看看IF函数在下图中应用,成绩分数>60分的 显示及格,反之显示不及格。
大家只要在B2单元格写入“=IF(A2=60,及格,不及格)”敲一下回车就可以看到结果显示及格了。
大家需要注意IF函数括号里面逗号分割开的3个参数,第一个参数代表条件,满足条件显示第二个参数,不满足条件显示第三个参数。EXCEL会根据>=60分这个条件自动判断及格还是不及格了。是不是很简单呢。
2、SUM 函数
求下图1、2、3之和,在D2单元格直接输入SUM(A2:C2),敲回车就看到结果了。注意:函数参数中的“:”代表 “到”的意思,A2:C2就是从A2到C2单元格
3、下面增加点难度,大家都知道SUM和IF函数了,那么把SUMIF组合到一起就是一个新的函数,这个函数有什么作用呢,我们来看下图
图中显示让我们计算苹果总数量,这个图里只有出现了3次苹果数量,用眼睛看一下就可以算出来结果12,但是如果一个上千行的表,出现上百次苹果,计算苹果的总数量怎么办呢,这就需要用到SUMIF函数了。
输入=SUMIF(A2:A7,苹果,B2:B7),敲回车,函数单元格结果显示12. 是不是很方便,我们来分析一下SUMIF的三个参数作用,第一个参数就是告诉Excel到哪一列找苹果,第二个参数,就是让Excel知道你要找的关键字“苹果”,第三个参数告诉Excel,苹果的数量到哪一列找。然后,找到所有苹果行数量累加,就是我们要的结果12了。
最后,给大家留一个疑问,如果想计算出上图苹果和草莓的总数,怎么做呢,是用SUMIF函数还是用SUMIFs函数呢,如果可以判断出来,说明你彻底理解,sumif函数在什么情况下使用了。
没有OFFICE2016的伙伴,可以私信回复“”2016“”获取
很多朋友都知道sumif函数是条件求和,99%的朋友只会其基本用法,我们通过案例来学习一些好玩的玩法!
函数基本语法
=sumif(条件区域,制定的条件,求和的区域)
通过以上方法,就可以实现对满足条件的区域进行求和
案例1 基本玩法
需要求出“北京”地区销售额总和
方法:输入公式 =sumif(C:C,C3,G:G),按下【enter】后,数据就可以快速进行求和了。
注意,参数二输入文本 “北京”也是一样的
案例2:高端玩法
问题描述:如图所示,表格中记录了员工从入职到离职的销售业绩。现在需要求出员工离职前1天(离职当天没有销售额),销售额的平均值。
遇到这种情况,大家该怎么办呢?
该案例无非就是需要求出每列数据空值上面的数据和的平均值,分别是81,30,22,25,56
思路:在每列的区域中进行查找,如果找到空值单元格,就输出其上面一行的数据,然后进行求和即可~
使用的公式为 =SUMIF(B3:H9,””,B2:H8) / 7
公式解释:
参数一:条件区域选择B3:H9
参数二:即满足的条件,输入的是 “”
参数三:求和区域,我们输入的参数选择的是条件区域的上一行
因此,该公式就表示在B列中进行查找空值,如果找到,就输出空值上一行的数据;同理,分别在C列,…., H列中进行查找空值,分别输出空值上一行的数据。最后再进行求和。
我们一起来看下动图效果吧
举一反三
如图所示,通过Sumif函数实现数据整理。具体效果如下图所示~
各位亲爱的读者朋友,你学会了吗?如果还有任何问题,欢迎留言或者私信交流Excel。
雷哥:自媒体【雷哥office】创始人,《竞争力:玩转职场Excel,从此不加班》作者,微软Excel专家认证,office培训师,职场老司机,分享职场经验 / 办公技能 / 提高职场效率
文:傲看今朝
在企业日常工作中,表哥表姐们最常遇见的问题就是条件求和,例如下面的要求:
公司本月邮寄费是多少?
公司财务部本月的成本支出是多少?
……
下面的表格是网友问我的问题,
原始表格
通过这样一张表格,他想知道如下结果:
1.李茂海总共销售了多少?
2.求销售数量大于等于500的总数量?
3.求姓名为两个两字的人的总销售数量?
4.求“晏阳阳”与“龙邦进”的总销售数量?
很显然,通过我们目前所学过的sum函数是很难回答这些问题的。对于此类问题,我的解决办法就是sumif函数。接下来我将给大家分享sumif函数的具体用法。
一、SUMIF函数解释
1.sumif为函数名,此函数用于条件求和;
2.range为条件区域,需为单元格区域引用,通常为单列单元格区域;
3.Criteria就是条件,只有满足了条件的单元格才会被加起来,此参数可以是表达式,文本或者单元格引用;
4.[sum_range]就是求和区域,对哪一列的单元格进行求和。此参数外面有中括号,说明此公式在某些情况下是可以省略的,什么时候省略,后面会有应用案例,这里不再赘述。
函数解释
二、简单运用sumif函数搞定条件求和
我们先动手解决第一个问题:求出李茂海的总销售数量?
数据表格
在下面的表格中,我们可以清晰地看到,A列中李茂海这个名字出现了很多次,我们需要将李茂海这个名字对应的B列的单元格值加起来,用sumif函数来解决非常简单:第一参数我们要判断A列单元格的值是否满足条件,如果满足条件,则将其同行对应的B列(求和区域)的值加起来。因此公式为:=sumif(A3:A19,”李茂海”,B3:B19),值得注意的函数中参数与参数务必用英文逗号隔开,而且出现文本时,务必用英文双引号引起来。
简单公式
轻松得到李茂海的总销售数量为2289。
三、sumif函数第三参数的省略
在Sumif函数中,第三参数被中括号([])括起来,意思是当条件区域和求和区域一致时,求和区域(第三参数)可以省略。例如文章开头提到的求单笔销售数量大于等于500的数量总和。我们将公式写成:=SUMIF(B3:B19,”>=500″)
第三参数省略
四、SUMIF函数中通配符的使用
Excel中的通配符是非常实用的,运用好这个东西,可以轻轻松松帮我们解决非常多的表格问题。在Excel中,通配符通常有3个,即:*,?,~,其中,*代表任意多个或者一个字符;?(英文半角符号)代表任意一个字符;~比较不常用,它没有特别的含义,仅为转义功能,配合*和?使用,在*和?前加~及表示*和?而非任意多个或者单个字符。本例中要求姓名为两个字,因此我们可以用两个?来表示。因此没有在掌握sumif函数通配符用法之前,我都是通过以下的操作方法来获得结果的:
右键单击下拉菜单选择筛选–单击A列列表的筛选按钮–文本筛选–等于–输入“??”(英文半角符号)–确定,然后选中B列的单元格区域,看看状态栏就得到结果了。
技巧法
这种方法勉强也可以,但是显然太笨,咱们只需要简单写一条sumif公式就解决了。本例子中的条件为:姓名为两个字,可以用通配符”??”来表示,条件区域为A3:A19,公式写成:=SUMIF(A3:A19,”??”,B3:B19)即可。
通配符
五、利用SUMIF函数做多条件求和
求晏阳阳和龙帮进的总销售数量?很多朋友第一反应都是分别求出他们俩的总销售数量,然后加起来。如下图所示:
加起来
这种方法当然没有错,只是公式太长,有没有更短的公式写法呢?答案是肯定的,下面就给大家介绍一种将公式写短的技巧。我们可以在第二参数做做文章,使用常量数组,如下图所示:
常量数组
公式的长度得到了极大的缩短,需要特别注意的2点:1.输入完公式,我们一定要按住Ctrl和Shift后再去敲回车,否则公式将计算错误;2.由于sumif函数第二参数使用了数组,因此此函数将得到龙帮进的销售数量和晏阳阳的销售数量两个结果,因此我们需要在sumif函数外套一个sum函数将这两个结果加起来,这样就可以轻松得到我们想要的结果了。
今天的分享就到这里,更多精彩内容,请随时点击收藏喜欢、或者评论我或者关注我。
- 1
1.sumif函数是用来根据指定条件对若干单元格进行求和。(即按条件求和)
END
- 1
2.sumif函数的语法格式
=sumif(range,criteria,sum_range)
Sumif(条件区域,求和条件,实际求和区域),第二个求和条件参数在第一个条件区域里。
END
- 1
3.如图,在A列中的众多发货平台(条件区域)中找出各个发货平台(求和条件)的发货总量(实际求和条件)
输入公式=SUMIF(A2:A13,D2,B2:B13)
公式说明,指定求和条件为成都发货平台,然后再众多发货平台(条件区域)中计算出成都发货平台的总发货量(求和区域)。
- 2
4.如图,我们通常会通过下拉填充公式就能快速把整列的发货量给计算出来,效果如图所示。
但是这时公式有问题,在计算上海发货平台的发货量的时候,这时的条件区域和实际求和区域是A3:A14,B3:B14,正确的应该是A2:A13,B2:B13。
这时就涉及到绝对引用和相对引用的问题,这里就不详细介绍,可以观看小编的经验Excel相对引用和绝对怎么用
- 3
5.如图,通过添加绝对引用,公式就正确了,条件区域和实际求和区域是A2:A13,B2:B13保持不变,效果如图所示。
END
6.=sumif(range,criteria,sum_range)
第二个求和条件参数在第一个条件区域里。
7.=sumif(range,criteria,sum_range)
第二个参数criteria如果是文本,要加引号。且引号为英文状态下输入。
8.=sumif(range,criteria,sum_range)
第二个参数criteria还可以使用通配符*。含义是只要含有成都字样的就进行求和。
9.Sumif(条件区域,求和条件,实际求和区域)
如图,我们在sumif函数是使用过程中,我们要选中条件区域和实际求和区域时,当数据上万条时,手动拖动选中时很麻烦,这时可以通过ctrl+shift+↓选中。
当几万条数据时,用下来填充的方式计算发货量很麻烦,可以观看小编的经验Excel怎么复制公式。