EXCEL中countifs函數(shù)用法大全

一、求各種類型單元格的個數(shù),,,,,,,,,,,,,,,

123,ILoveYou,,,,,真空單元格：指什么內(nèi)容也沒有的單元格；,,,,,,,,,

Excel,,,真空單元格,,,假空單元格：指0字符的空文本，一般是由網(wǎng)上下載來的或公式得來的，姑且稱之為假空。,,,,,,,,,

,jfkd,,假空單元格,,,,,,,,,,,,

,中國,,文本型單元格,,,,,,,,,,,,

大家好,MyGod,,數(shù)字型單元格,,,所求類型單元格,結果,公式,,,,,,,

同志,有志者事竟成,,,,,真空單元格,2,"=COUNTIF(data1,""="")",,,,,,,

,恨蒼天,,,,,真空+假空單元格,5,"=COUNTIF(data1,"""")",相當于countblank()函數(shù),,,5,,,

22.56,立體,,,,,非真空單元格,20,"=COUNTIF(data1,""<>"")",相當于counta()函數(shù),,,20,,,

-1,88,,,,,文本型單元格,14,"=COUNTIF(data1,""*"")",假空單元格也是文本型單元格,,,,,,

,,,,,,區(qū)域內(nèi)所有單元格,22,"=COUNTIF(data1,""<>"""""")",,,,,,,

Home,473,,,,,數(shù)字型單元格,6,=COUNT(data1),不是countif()，這里順便點一下。,,,,,,

,,,,,,,,,,,,,,,,

二、求><=某個值的單元格個數(shù),,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

50,-31,,,,,大于50,8,"=COUNTIF(data2,"">50"")",,,,,,,

-30,73,,,,,等于50,4,"=COUNTIF(data2,50)",,,,,,,

37,58,,,,,小于50,18,"=COUNTIF(data2,""<50"")",,,,,,,

78,36,,,,,大于或等于50,12,"=COUNTIF(data2,"">=50"")",,,,,,,

-49,-75,,,,,小于或等于50,22,"=COUNTIF(data2,""<=50"")",,,,,,,

50,50,,,50,,大于E5單元格的值,8,"=COUNTIF(data2,"">""&$E$21)",,,,,,,

-99,-59,,,,,等于E5單元格的值,4,"=COUNTIF(data2,$E$21)",,,,,,,

-58,-96,,,,,小于E5單元格的值,18,"=COUNTIF(data2,""<""&$E$21)",,,,,,,

82,50,,,,,大于或等于E5單元格的值,12,"=COUNTIF(data2,"">=""&$E$21)",,,,,,,

93,91,,,,,小于或等于E5單元格的值,22,"=COUNTIF(data2,""<=""&$E$21)",,,,,,,

-2,56,,,,,,,,,,,,,,

20,78,,,,,,,,,,,,,,

-2,-36,,,,,,,,,,,,,,

-67,-91,,,,,,,,,,,,,,

-38,-72,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

三、等于或包含某N個特定字符的單元格個數(shù),,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

ABCDE,KB,,,,,兩個字符,6,"=COUNTIF(data3,""??"")",,,,,,,

JJKFDLb,urk,,,,,兩個字符并且第2個是B,2,"=COUNTIF(data3,""?B"")",,,,,,,

OPEN,ab,,,,,包含B,4,"=COUNTIF(data3,""*B*"")",,,,,,,

THANK,同志,,,,,第2個字符是B,3,"=COUNTIF(data3,""?B*"")",,,,,,,

COUNTIF,excelhome,,好,,,等于“你好”,2,"=COUNTIF(data3,""你好"")",,,,,,,

你好,你好,,,,,包含D38單元格的內(nèi)容,5,"=COUNTIF(data3,""*""&D38&""*"")",,,,,,,

我好,中國人民,,,,,第2字是D38單元格的內(nèi)容,3,"=COUNTIF(data3,""?""&D38&""*"")",,,,,,,

大家好嗎,解放軍,,,,,,,,,,,,,,

acdkl,同志們好,,,,,注：countif()函數(shù)對英文字母不區(qū)分大小寫,,,,,,,,,

,,,,,,,,,,,,,,,,

四、不連續(xù)區(qū)域求個數(shù),,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

60,,,,,,三個區(qū)域中>=60,4,"=SUM(COUNTIF(INDIRECT({""a46:a48"",""b48:b50"",""c47:c48""}),"">=60""))",,,,,,,

70,,70,,,,三個區(qū)域中=70,3,"=SUM(COUNTIF(INDIRECT({""a46:a48"",""b48:b50"",""c47:c48""}),70))",,,,,,,

30,70,50,,,,三個區(qū)域中<60,4,"=SUM(COUNTIF(INDIRECT({""a46:a48"",""b48:b50"",""c47:c48""}),""<60""))",,,,,,,

,33,,,,,,,,,,,,,,

,51,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

五、兩個條件求個數(shù),,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

11,8,,,,,>10并且<=15,7,"=SUM(COUNTIF(data4,"">""&{10,15})*{1,-1})",,,,,,,

4,10,,,,,>=10并且<15,8,"=SUM(COUNTIF(data4,"">=""&{10,15})*{1,-1})",,,,,,,

3,7,,,,,>=10并且<=15,9,"=SUM(COUNTIF(data4,{"">=10"","">15""})*{1,-1})",,,,,,,

14,10,,,,,>10并且<15,6,"=SUM(COUNTIF(data4,{"">10"","">=15""})*{1,-1})",,,,,,,

18,15,,,,,,,,,,,,,,

13,3,,,,,,,,,,,,,,

12,8,,,,,,,,,,,,,,

18,13,,,,,,,,,,,,,,

13,3,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

六、動態(tài)區(qū)域求個數(shù),,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

班級,姓名,成績,班級,成績,,,,,,,,,,,

1,子,60,3,70,,,,,,,,,,,

1,丑,70,,,,,,,,,,,,,

1,寅,85,,,,班級=D68，成績大于=E68,4,"=COUNTIF(OFFSET(C67,MATCH(D68,A68:A81,),,COUNTIF(A68:A81,D68)),"">=""&E68)",,,,,,,

1,卯,91,公式解析：,,,,,,,,,,,,

2,辰,88,,用offset()生成每個班級的成績區(qū)域（如E74:E80)，再用countif()計數(shù)。,,,,,,,,,,,

2,巳,45,,,,,,,,,,,,,

2,午,70,,55,"=OFFSET(C67,MATCH(D68,A68:A81,),,COUNTIF(A68:A81,D68))",,,,,,,,,,

3,未,55,,100,,,,,,,,,,,

3,申,100,,96,,,,,,,,,,,

3,酉,96,,73,,,,,,,,,,,

3,戌,73,,66,,,,,,,,,,,

3,亥,66,,78,,,,,,,,,,,

3,甲,78,,64,,,,,,,,,,,

3,乙,64,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

七、用countif()排序(一般用法及生成內(nèi)存數(shù)組)示例,,,,,,,,,,,,,,,

數(shù)據(jù)區(qū)域,一般用法,內(nèi)存數(shù)組,,,,,,,,,,,,,

金世遺,古龍,古龍,7,"=COUNTIF(A85:A92,"">=""&A85:A92)",,,,,,,,,,,

金逐流,金世遺,金世遺,5,,,,,,,,,,,,

金庸,金庸,金庸,6,,,,,,,,,,,,

木婉青,金逐流,金逐流,2,,,,,,,,,,,,

古龍,梁羽生,梁羽生,8,,,,,,,,,,,,

臥龍生,令弧沖,令弧沖,1,,,,,,,,,,,,

梁羽生,木婉青,木婉青,4,,,,,,,,,,,,

令弧沖,臥龍生,臥龍生,3,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

Taxis,"=T(OFFSET($A$85,RIGHT(LARGE(COUNTIF($A$85:$A$92,"">=""&$A$85:$A$92)*10^5+ROW($A$85:$A$92)-ROW($A$85),ROW(OFFSET($A$1,,,ROWS($A$85:$A$92)))),5),))",,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

800004,"=LARGE(D85:D92*10^5+ROW($A$85:$A$92)-ROW($A$85),ROW(OFFSET($A$1,,,ROWS($A$85:$A$92))))",,,,,,,,,,,,,,

700000,,,,,,,,,,,,,,,

600002,,00004,"=RIGHT(A96:A103,5)",,,,,,,,,,,,

500001,,00000,,,,,,,,,,,,,

400006,,00002,,,,,,,,,,,,,

300007,,00001,,,,,,,,,,,,,

200003,,00006,,,,,,,,,,,,,

100005,,00007,,,,,,,,,,,,,

,,00003,,,,,,,,,,,,,

,,00005,,,,,,,,,,,,,

公式解析,,,,,,,,,,,,,,,

,"用COUNTIF($A$85:$A$92,"">=""&$A$85:$A$92)生成D85:D92的數(shù)組再排序",,,,,,,,,,,,,,

,*10^5是因為行號最大65536不超過100000，用*一個足夠大的數(shù)使行號大小不會影響排序結果。,,,,,,,,,,,,,,

,"再用right(,5)取出各行應該得到的偏移量",,,,,,,,,,,,,,

,最后用T(Offset())得到內(nèi)存數(shù)組,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

八、用countif()的三維引用取得不重復值示例,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

數(shù)據(jù)區(qū)域,一般用法,內(nèi)存數(shù)組,,,,,,,,,,,,,

甲,甲,甲,0.25,"=1/COUNTIF(data5,data5)",,,,,,,,,,,

乙,乙,乙,1,,,,,,,,,,,,

丙,丙,丙,1,,,,,,,,,,,,

丁,丁,丁,0.5,,,,,,,,,,,,

戊,戊,戊,0.5,,,,,,,,,,,,

己,己,己,0.5,,,,,,,,,,,,

庚,庚,庚,0.5,,,,,,,,,,,,

辛,辛,辛,1,,,,,,,,,,,,

壬,壬,壬,1,,,,,,,,,,,,

癸,癸,癸,1,,,,,,,,,,,,

甲,,#N/A,0.25,,,,,,,,,,,,

甲,,#N/A,0.25,,,,,,,,,,,,

甲,,#N/A,0.25,,,,,,,,,,,,

丁,,#N/A,0.5,,,,,,,,,,,,

戊,,#N/A,0.5,,,,,,,,,,,,

己,,#N/A,0.5,,,,,,,,,,,,

庚,,#N/A,0.5,,,,,,,,,,,,

,,,10,=SUM(D115:D131),,,,,,,,,,,

,,,,,,,,,,,,,,,,

數(shù)據(jù)區(qū)域,三維用法,一般用法,行數(shù),,,,,,,,,,,,

甲,1,1,1,,,,,,,,,,,,

乙,1,1,2,,,,,,,,,,,,

丙,1,1,3,,,,,,,,,,,,

丁,1,1,4,,,,,,,,,,,,

戊,1,1,5,,,,,,,,,,,,

己,1,1,6,,,,,,,,,,,,

庚,1,1,7,,,,,,,,,,,,

辛,1,1,8,,,,,,,,,,,,

壬,1,1,9,,,,,,,,,,,,

癸,1,1,10,,,,,,,,,,,,

甲,2,2,11,,,,,,,,,,,,

甲,3,3,12,,,,,,,,,,,,

甲,4,4,13,,,,,,,,,,,,

丁,2,2,14,,,,,,,,,,,,

戊,2,2,15,,,,,,,,,,,,

己,2,2,16,,,,,,,,,,,,

庚,2,2,17,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

x,"=T(OFFSET($A$115,SMALL(IF(COUNTIF(OFFSET($A$115,,,ROW(data5)-ROW($A$114)),data5)=1,ROW(data5)),ROW(INDIRECT(""1:""&SUM(1/COUNTIF(data5,data5)))))-ROW($A$115),))",,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

公式解析,,,,,,,,,,,,,,,

,"用SUM(1/COUNTIF(data5,data5))求數(shù)據(jù)區(qū)域中不重復值個數(shù)（D115:D132)",,,,,,,,,,,,,,

,"用COUNTIF(OFFSET($A$115,,,ROW(data5)-ROW(A$114)),data5)=1判斷單元格內(nèi)容是否重復，重復的結果>1",,,,,,,,,,,,,,

,"其結果就是=COUNTIF(A$135:A135,A135)公式下拖,詳細見B135:C151",,,,,,,,,,,,,,

,以本題為例，ROW(data5)-ROW(A$114)產(chǎn)生1到17的一個垂直數(shù)組，D135:D151,,,,,,,,,,,,,,

,用small(if())取得不重復值的行號(offset()的行偏移量)，如果countif()=1(即不重復)就取行號，否則就false，small()函數(shù)忽略邏輯值和文本值。,,,,,,,,,,,,,,

,最后再用T(Offset())生成內(nèi)存數(shù)組。,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

九、用countif()進行多表匯總計數(shù)示例,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

姓名,出現(xiàn)次數(shù),,,,,,,,,,,,,,

張三,12,"=SUM(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT(""1:12"")),""[dbnum1]d月"")&""!A2:A100""),A167))",,,,,,,,,,,,,

李四,10,"=SUM(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT(""1:12"")),""[dbnum1]d月"")&""!A2:A100""),A168))",,,,,,,,,,,,,

王五,11,"=SUM(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT(""1:12"")),""[dbnum1]d月"")&""!A2:A100""),A169))",,,,,,,,,,,,,

周六,12,"=SUM(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT(""1:12"")),""[dbnum1]d月"")&""!A2:A100""),A170))",,,,,,,,,,,,,

鄭七,10,"=SUM(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT(""1:12"")),""[dbnum1]d月"")&""!A2:A100""),A171))",,,,,,,,,,,,,

李八,10,"=SUM(COUNTIF(INDIRECT(TEXT(ROW(INDIRECT(""1:12"")),""[dbnum1]d月"")&""!A2:A100""),A172))",,,,,,,,,,,,,

,,,,,,,,,,張三,李四,王五,周六,鄭七,李八

一月,張三,周六,鄭七,李八,,,,,,1,,,1,1,1

二月,張三,李四,王五,周六,鄭七,李八,,,,1,1,1,1,1,1

三月,張三,李四,王五,周六,鄭七,李八,,,,1,1,1,1,1,1

四月,張三,王五,周六,鄭七,李八,,,,,1,,1,1,1,1

五月,張三,李四,王五,周六,李八,,,,,1,1,1,1,,1

六月,張三,李四,王五,周六,鄭七,李八,,,,1,1,1,