COUNTIF函数常规用法
一、求各种类型单元格的个数(1)求真空单元格单个数:=COUNTIF(data,"=")
(2)真空+假空单元格个数:=COUNTIF(data,"")相当于countblank()函数
(3)非真空单元格个数:=COUNTIF(data,">")相当于counta()函数
(4)文本型单元格个数:=COUNTIF(data,"*")假空单元格也是文本型单元格
(5)区域内所有单元格个数:=COUNTIF(data,">""")
(6)逻辑值为TRUE的单元格数量：=COUNTIF(data,TRUE)
小说明：
EXCEL单元格内数据主要有以下几类:数值型,文本型,逻辑型,错误值型。其中时间类型也是一种特殊的数值。文本类型的数字是文本型。空单元格：指什么内容也没有的单元格，姑且称之为真空。假空单元格：指0字符的空文本，一般是由网上下载来的或公式得来的，姑且称之为假空。date指单元格区域，该参数不能是数组。
二、求><=某个值的单元格个数
(1)大于50：=COUNTIF(data,">50")
(2)等于50：=COUNTIF(data,50)
(3)小于50：=COUNTIF(data,"<50")
(4)大于或等于50：=COUNTIF(data,">=50")
(5)小于或等于50：=COUNTIF(data,"<=50")
(6)大于E5单元格的值：=COUNTIF(data,">"&$E$5)
(7)等于E5单元格的值：=COUNTIF(data,$E$5)
(8)小于E5单元格的值：=COUNTIF(data,"<"&$E$5)
(9)大于或等于E5单元格的值：=COUNTIF(data,">="&$E$5)
(10)小于或等于E5单元格的值：=COUNTIF(data,"<="&$E$5)
三、等于或包含某N个特定字符的单元格个数
(1)两个字符：=COUNTIF(data,"??")
(2)两个字符并且第2个是B:=COUNTIF(data,"?B")
(3)包含B:=COUNTIF(data,"*B*")
(4)第2个字符是B:=COUNTIF(data,"?B*")
(5)等于“你好”:=COUNTIF(data,"你好")
(6)包含D3单元格的内容:=COUNTIF(data,"*"&D3&"*")
(7)第2字是D3单元格的内容:=COUNTIF(data,"?"&D3&"*")
注：countif()函数对英文字母不区分大小写，通配符只对文本有效
四、两个条件求个数
(1)>10并且"&{10,15})*{1,-1})
(2)>=10并且="&{10,15})*{1,-1})
(3)>=10并且=10",">15"})*{1,-1})
(4)>10并且10",">=15"})*{1,-1})
注：一般多条件计数使用SUMPRODUCT函数，以上方法较少使用，仅供参考。
补充：三个区域计数：三个区域中=60
五、各种特殊统计要求的计算A1:A100为存放数据的区域
(1)非空文本(仅包括可键入文本:=COUNTIF(A1:A100,">=!")
(2)所有非空文本=COUNTIF(A1:A100,">=!")+COUNTIF(A1:A100,">="&CHAR(1))-COUNTIF(A1:A100,">=")或{=SUM(COUNTIF(A1:A100,">="&{"!",""})*{1,-1})+COUNTIF(A1:A100,">="&CHAR(1))}即文本型-含" "(空格)数量
(3)全部可见单元格{=SUM(N(IF(ISERROR(A1:A100),1,SUBSTITUTE(A1:A100," ",""))>""))}，即全部看上去不是空值的单元格(=全部记录-真空-空格)
(4)有效可见单元格=COUNTIF(A1:A100,">=!")+COUNTIF(A1:A100,">="&CHAR(1))-COUNTIF(A1:A100,">= ")+COUNT(A1:A100)，即所有非空文+数字型数据之和
(5)全部不见单元格(真空+空格+空文本)=COUNTIF(A1:A100,"")+COUNTIF(A1:A100,">=!")+COUNTIF(A1:A100,">=")
(6)空格=COUNTIF(A1:A100,">=")-COUNTIF(A1:A100,">=!")
(7)空文本""=COUNTIF(A1:A100,"")-COUNTIF(A1:A100,"=")
(8)逻辑与错误=COUNTIF(A1:A100,">")-COUNTIF(A1:A100,"*")-COUNT(A1:A100)
绛旓細=SUM(COUNTIF(A1:A10,{3,-3}))鍥涖佸紩鐢鍑芥暟缁撴灉缁熻 23銆佽繑鍥炲寘鍚綋鍓嶆棩鏈熺殑鍗曞厓鏍兼暟閲 =COUNTIF锛圓:A锛孴ODAY()锛24銆佽繑鍥炲ぇ浜庡钩鍧囧肩殑鍗曞厓鏍兼暟閲 =COUNTIF锛圓:A锛>&AVERAGE(A:A))璇风偣鍑昏緭鍏ュ浘鐗囨弿杩帮紙鏈澶18瀛楋級
绛旓細1銆佷互涓嬭〃涓轰緥婕旂ずcountif鍑芥暟鐨鐢ㄦ硶锛屾眰寰楀垎90鍒嗕互涓婄殑瀛︾敓鏁伴噺銆2銆佸湪C2鍗曞厓鏍艰緭鍏ュ叕寮忊=COUNTIF(B2:B20,">90")鈥濓紝鍏朵腑鈥淏2:B20鈥濊〃绀虹粺璁″尯鍩燂紝鈥>90鈥濊〃绀哄緱鍒嗗ぇ浜90鐨勬潯浠躲3銆佸緱鍒扮殑缁撴灉鏄5锛岀瓟妗堟纭4銆佹眰寰楀垎澶т簬绛変簬80鍒嗗苟涓斿皬浜90鍒嗙殑瀛︾敓鏁般5銆佸湪C4鍗曞厓鏍艰緭鍏ュ叕寮忊=COUNTIF(B2...
绛旓細1銆佸寘鍚12鐨勫崟鍏冩牸鏁伴噺 =COUNTIF(A锛欰锛12)2銆佸寘鍚礋鍊肩殑鍗曞厓鏍兼暟閲 =COUNTIF(A锛欰锛"<0")3銆佷笉绛変簬0鐨勫崟鍏冩牸鏁伴噺 =COUNTIF(A锛欰锛"<>0")4銆佽繑鍥炲ぇ浜5鐨勫崟鍏冩牸鏁伴噺 =COUNTIF(A锛欰锛">5")5銆佽繑鍥炵瓑浜嶣1涓唴瀹圭殑鍗曞厓鏍兼暟閲 =COUNTIF(A锛欰锛孊1)6銆佽繑鍥炲ぇ浜嶣1涓唴瀹圭殑鍗曞厓鏍兼暟閲 =C...
绛旓細(1) 澶т簬50=COUNTIF(鏁版嵁鍖,">50")(2) 绛変簬50=COUNTIF(鏁版嵁鍖,"=50")(3) 灏忎簬50 =COUNTIF(鏁版嵁鍖,"<50")(4) 澶т簬鎴栫瓑浜50 =COUNTIF(鏁版嵁鍖,">=50")(5) 灏忎簬鎴栫瓑浜50 =COUNTIF(鏁版嵁鍖,"<=50")(6) 澶т簬E5鍗曞厓鏍肩殑鍊 =COUNTIF(鏁版嵁鍖,">"&$E$5)(7) 绛変簬E5鍗曞厓鏍肩殑鍊 ...
绛旓細countif鍑芥暟鐨勭敤娉曞強鍏舵剰涔夊涓嬶細涓銆佺敤娉 1銆佺粺璁℃垚缁╁ぇ浜90鍒嗕汉鏁 杈撳叆鍏紡=COUNTIF(B2:B10,">90")銆2銆佺粺璁℃垚缁╁湪80~90鍒嗕箣闂 缁熻鎴愮哗澶т簬80灏忎簬绛変簬90涔嬮棿鐨勪汉鏁锛岃緭鍏ュ叕寮=SUM(COUNTIF(B2:B10,{">80",">90"})*{1,-1})銆3銆佺粺璁″閮戠殑浜烘暟 杈撳叆鍏紡=COUNTIF(A2:A10,"閮*")銆*...
绛旓細Countif :璁＄畻鍖哄煙涓弧瓒崇粰瀹氭潯浠剁殑鍗曞厓鏍肩殑涓暟 璇硶锛countif(range,criteria)Range锛氫负闇瑕佽绠楀叾涓弧瓒虫潯浠剁殑鍗曞厓鏍兼暟鐩殑鍗曞厓鏍煎尯鍩 Criteria锛氫负纭畾鍝簺鍗曞厓鏍煎皢琚绠楀湪鍐呯殑鏉′欢锛屽叾褰㈠紡鍙互涓烘暟瀛椼佽〃杈惧紡鎴栨枃鏈 渚嬪锛氬厛璁＄畻鍑衡滃ぇ浜79鐨勪釜鏁扳
绛旓細濡傚浘涓嬫墍绀猴紝鍒ゆ柇鍛樺伐鏄惁閲嶅杈撳叆锛屽湪D2鍗曞厓鏍艰緭鍏ュ叕寮忥細=IF(COUNTIF($A$2:$A$10,A2)>1,鈥濇槸鈥,鈥濆惁鈥)锛屽苟鍚戜笅濉厖銆備互涓婏紝灏辨槸灏忕紪涓哄ぇ瀹跺綊绾崇殑鍏充簬COUNTIF鍑芥暟鍏紡鐢ㄦ硶瀹炰緥銆傚洓銆丆OUNTIF鍜孋OUNTIFS鍖哄埆COUNTIF鏄鎸囧畾鍖哄煙涓鍚堟寚瀹氭潯浠剁殑鍗曞厓鏍艰鏁扮殑涓涓嚱鏁般侰OUNTIFS鏄潵璁＄畻澶氫釜鍖哄煙涓...
绛旓細COUNTIF鍑芥暟鐨浣跨敤鏂规硶锛1锛屾剰涔夈侰OUNTIF鍑芥暟琛ㄧず鈥滃湪鎸囧畾鍖哄煙涓寜鎸囧畾鏉′欢瀵瑰崟鍏冩牸杩涜璁℃暟鈥濄2锛屼娇鐢ㄨ娉曟牸寮忋=countif锛坮ange锛宑riteria锛夊叾涓弬鏁皉ange 琛ㄧず鏉′欢鍖哄煙鈥斺斿鍗曞厓鏍艰繘琛岃鏁扮殑鍖哄煙銆傚弬鏁癱riteria 琛ㄧず鏉′欢鈥斺旀潯浠剁殑褰㈠紡鍙互鏄暟瀛椼佽〃杈惧紡鎴栨枃鏈瓑銆3锛屼娇鐢ㄤ妇渚嬶細濡備笅鍥俱=countif锛圓2:...
绛旓細Countif鍑芥暟鏄疢icrosoft Excel涓鎸囧畾鍖哄煙涓鍚堟寚瀹氭潯浠剁殑鍗曞厓鏍艰鏁扮殑涓涓嚱鏁帮紝鍦╓PS锛孍xcel2003鍜孍xcel2007绛夌増鏈腑鍧囧彲浣跨敤銆傝鍑芥暟鐨勮娉曡鍒欏涓嬶細countif锛坮ange锛宑riteria锛鐢ㄦ硶锛氫竴銆佹眰鍚勭绫诲瀷鍗曞厓鏍肩殑涓暟 (1) 姹傜湡绌哄崟鍏冩牸涓暟:=COUNTIF(鏁版嵁鍖,"=")(2) 闈炵湡绌哄崟鍏冩牸涓暟: =COUNTIF(...
绛旓細鍦╡xcel涓紝countif鍑芥暟鏄寜鎸囧畾鐨勬潯浠跺鍗曞厓鏍艰繘琛岃鏁扮粺璁°鐢ㄦ硶濡備笅锛氬伐鍏/鍘熸枡锛欴ell娓稿專G15銆亀in10銆乪xcel2013 1銆佹墦寮excel琛ㄦ牸锛屾柊寤轰竴涓鐢熸垚缁╄〃锛岃〃涓寘鍚鐢熷鍙峰拰瀛︾敓鎴愮哗锛屽涓嬪浘鎵绀恒2銆佸湪Excel琛ㄦ牸涓紝濡傛灉瑕佺粺璁″垰濂60鍒嗭紝灏遍渶瑕佺敤鍒癱ountif鍑芥暟浜嗭紝countif鍑芥暟鏄敤鏉ヨ绠楀尯鍩熶腑婊¤冻...
