1.统计第几次重复
2.统计不重复的数量
如图2所示,要统计不重复的部门数量,其公式为:=SUM(1/COUNTIF(B2:B10,B2:B10)),然后同时按下数组三键Ctrl+Shift+Enter,或者写成:=SUMPRODUCT(1/COUNTIF(B2:B10,B2:B10)),无需按数组三键。COUNTIF(B2:B10,B2:B10)第二个参数B2:B10为连续的单元格条件区域,会形成条件数组{"人事部";"人事部";"行政部";"推广部";"推广部";"推广部";"客服部";"客服部";"售后部"},COUNTIF(B2:B10,B2:B10)会对每一个部门进行计数,结果为:{2;2;1;3;3;3;2;2;1},然后用1除以这个数组,结果为:{0.5;0.5;1;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;1},例如说“人事部”重复两次,用1除后,每个人事部就会统计为0.5次,再用SUM函数求和,那么两个人事部就是1次,其他部门以此类推,这样就能统计到不重复次数。
3.通配符模糊统计
如图3所示,要统计姓“张”员工的数量,其公式为:=COUNTIF(A2:A11,"张*"),*表示任意匹配多个通配符,"张*"就能匹配到区域中所有以张为开头的字符串。如图3-1所示,要统计非“张”性员工人数,其公式为:=COUNTIF(A2:A10,"<>张*"),如果要引用单元格,其公式为:=COUNTIF(A2:A10,"<>"&D5)。
4.指定数量区间内统计
如图4所示,要求“销量低于100的有几人”,其公式为:=COUNTIF(C2:C11,"<100"),要求“销量高于等于200有几人”,其公式为:=COUNTIF(C2:C11,">=200"),要求“销量在120-200之间的个数”,不包括120和200,其公式为:=COUNTIF(C2:C11,">120")-COUNTIF(C2:C11,">=200"),用大于120的销量次数减去大于等于200销量的次数,结果即为120-200的销量个数。
5.防止重复录入
6.统计空值个数
如图7所示,当销量大于等于100时,可获得提成比例,其公式为:=IF(C5>=100,C5*10%,""),当销量不达标时提成为空,现在要统计提成为空的人数,其公式为:=COUNTIF(D2:D10,""),“”表示为空值条件。
7.忽略错误值或空值统计
9.统计非空单元格不忽略错误值
如图9所示,要统计非空单元格并不忽略错误值,其公式为:=COUNTIF(C2:C10,"<>"),"<>"表示不等于非空单元格。
10.重复值填充背景色
如图10所示,当我输入重复值时,会自动填充背景色。如图11所示,点击开始——条件格式——新建规则——选择【使用公式确定要设置格式的单元格】,在下面输入公式:=COUNTIF(A:A,A1)>1,在【格式】里面选择合适的背景色,点击确定即可。
11.判断是否重复
如图12所示,判断员工是否重复输入,在D2单元格输入公式:=IF(COUNTIF($A$2:$A$10,A2)>1,"是","否"),并向下填充。
以上,就是小包为大家归纳的关于COUNTIF函数的11种高阶用法,关注小包,后面持续为大家分享Excel职场实用技巧。