countif函数多条件求和
COUNTIF函数的常见⽤法总结!
你好,我是刘卓。欢迎来到我的公号,excel函数解析。今天来分享下countif函数的⼀些⽤法。countif函数是⼀个强⼤的统计函数,在⼯作中有着⼴泛的应⽤。
-01-
函数说明
countif主要⽤于统计满⾜某个条件的单元格数量,语法如下,有2个参数。
COUNTIF(range, criteria)
第1参数range为必需,是要统计数量的单元格区域。必需是单元格区域,不能是数组,包括常量数组和公式返回的数组都是不⾏的。range这个单词的意思就是范围、区域。
第2参数criteria为必需,是要满⾜的条件,可以是数字、表达式、单元格引⽤或⽂本字符串。它⽀持通配符*和?。
-02-
常见⽤法
1.给部门添加序号
下图展⽰的是某公司的员⼯信息表,要求根据B列的部门编写序号,遇到不同的部门,序号重新从1开始编写,结果如A列所⽰。在A3单元格输⼊下⾯的公式,向下填充。
=COUNTIF(B$3:B3,B3)
countif的第1参数中,第1个B3的⾏号是固定的,第2个B3的⾏号不固定,当公式向下填充时,区域会动态扩展。
别看这个公式很简单,它的⽤途还是挺多的。可以添加序号,也可以⽤来判断是第⼏次出现的,还可以进⼀步判断是否是重复出现的(结果⼤于1的就是重复的),还可以提取不重复值(把结果等于1的提取或筛选出来)。
2.出A列中有,D列中没有的姓名
从A列中出D列中没有的姓名,结果如下图标绿的单元格所⽰。该如何出来呢?⽅法有很多的,当然今天是⽤countif的⽅法。
添加⼀个辅助列,在B17单元格输⼊下⾯的公式,向下填充。
=COUNTIF(D$17:D$22,A17)
注意countif的区域不要选反了,选A列的区域还是选D列的区域,⾃⼰想⼀想。如果只想要⽤颜⾊标记出来,也可以⽤条件格式来设置。
3.常量数组和通配符的⽤法
下图左表是各⼩组成员的成绩表,共有3个问题。先看第1问,统计1组和2组成员的总⼈数。输⼊下⾯的公式,完成。
=SUM(COUNTIF(B31:B40,{"1组";"2组"}))
countif的第2参数{"1组";"2组"}是个常量数组,有2个元素,分别是1组和2组。那么countif函数返回的结果也有2个值,分别是1组的⼈数和2组的⼈数,结果为{3;3}。最后⽤sum函数对两组
的⼈数求和。
来看第2问,统计成绩⼤于等于80且⼩于90的⼈数。可以看到只有1个88满⾜条件。输⼊下⾯的公式,完成。
=SUM(COUNTIF(C31:C40,">="&{80,90})*{1,-1})
思路是⽤⼤于等于80的个数减去⼤于等于90的个数,就是⼤于等于80且⼩于90的个数,最好画个数轴来看下的。
上⾯的公式也是常量数组的⽤法,countif返回的结果也有2个值,结果是{4,3},乘以{1,-1}得到的结果为{4,-3},最后⽤sum求和,其实就是4减3。
也可以⽤下⾯的公式,简单易懂。
=COUNTIF(C31:C40,">=80")-COUNTIF(C31:C40,">=90")
最后来看第3问,统计姓名不是两个字的⼈数。姓名是两个字的已经⽤红⾊箭头标出,共有2个,剩下的共有8个。输⼊下⾯的公式,完成。
=COUNTIF(A31:A40,"<>??")
通配符*和?只能代表⽂本,*代表任意的⽂本,长度不限,字符不限。?代表任意单个字符,??就代表任意两个字符,"<>??"表⽰不是两个字符的⽂本。
4.统计不重复部门的个数
输⼊下⾯的公式,按ctrl+shift+enter三键完成。
=SUM(1/COUNTIF(B45:B54,B45:B54))
这个公式⽤countif统计出B45:B54这个区域中各个部门的个数,结果为{3;3;3;2;2;3;3;3;2;2}。
⽐如⼈事部对应的个数都是3,⽤1除将⼈事部的3个3变成3个三分之⼀,最后⽤sum求和,将3个三分之⼀合成1,这样就把⼈事部算做1个不重复的。其他部门也是同样的算法,就是把n个n分之⼀相加。
5.中国式排名
中国式排名如果有名次⼀样,并列排名,不会占位后⾯的名次。⽐如100,100,90会排名为1,1,2,⽽不是1,1,3。在D59单元格输⼊下⾯的公式,按ctrl+shift+enter三键,向下填充。
=SUM((C$59:C$68>=C59)/COUNTIF(C$59:C$68,C$59:C$68))

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。