Excel CountIf函數的使用方法，包含與If、Sum組合實現統計不重復與多條件實例

在 excel 中，COUNTIF函數用于統計指定范圍內滿足某個條件的單元格個數；它只有兩個參數，一個是統計范圍，另一個是條件，其中條件不能超過 255 個字符，如果超過需要分開。條件中僅為 *、?、>、< 等，可以完成一些特殊的統計。

COUNTIF函數如果要實現多條件的的統計需要與 If、OffSet、Sum函數組合，并且有時條件需用數組，例如：Sum + If + CountIf函數組合實現統計不為空且不包含重復值的個數，Sum + CountIf + 數組條件實現同一范圍內滿足兩個條件的統計。

一、excel CountIf函數語法

1、表達式：COUNTIF(Range, Criteria)

中文表達式：COUNTIF(統計區域, 條件)

2、說明：

A、Range 可以是數組或單元格的引用。

B、條件可以是單個數字或文本，也可以為表達式或函數；但條件中不能超過 255 個字符，否則會返回錯誤；如果條件超過 255 個字符，可以把它們拆分為多個長字符串，然后用 Concatenate 函數或連接運算符 & 把它們連接起來，例如："長字符中1……"&"長字符串2……”。另外，條件忽略字母大小寫。

C、在條件中，可以使用通配符問號（?）和星號（*），問號表示任意一個字符，星號表示任意一個或一串字符；如果要查找問號或星號，需要在它們前面加轉義字符~；如：查找問號，表達式應該這樣寫 ~?；查找星號，表達式應該這樣寫 ~*。

D、統計文本個數時，不能包含前導與尾部空格，也不能出現單雙引號不一致和非打印字符；否則，CountIf函數可能返回不可預期的值。統計文本前或后有空格時，可以用Trim函數把它們去掉；有非打印字符時，可以用Clean函數把它們去掉 。

二、Excel CountIf函數的使用方法及實例

（一）條件為文本和統計空值的實例

1、雙擊 D2 單元格，把公式 =COUNTIF(C1:C11,"經理") 復制到 D2，按回車，返回統計結果 3；雙擊 D2，把雙引號內的“經理”去掉，按回車，返回統計結果 1；操作過程步驟，如圖1所示：

圖1

2、公式說明：

在 =COUNTIF(C1:C11,"經理") 中，C1:C11 為統計區域，"經理"為條件，意思是：在 C1 至 C11 中統計“經理”的個數；當條件為 "" 時，統計空值的個數，C1:C11 中只有一個空單元格，因此統計結果為 1。

（二）條件為表達式的實例

1、假如要統計水果銷量大于等于 1000 的個數。雙擊 D8 單元格，把公式 =COUNTIF(D2:D7,">=1000") 復制到 D8，按回車，返回統計結果 5；操作過程步驟，如圖2所示：

圖2

2、公式 =COUNTIF(D2:D7,">=1000") 中的條件為 ">=1000"，意思是：在 D2:D7 中統計數值大于等于 1000 的個數；另外，">=1000" 還可以這樣寫 ">"&"1000"，即把大于號與數字分開寫，再用連接運算符把它們連接起來。

（三）條件中有函數的實例

1、假如要統計水果銷量大于平均值的個數。雙擊 D8 單元格，把公式 =COUNTIF(D2:D7,">"&AVERAGE(D2:D7)) 復制到 D8，按回車，返回統計結果 3；操作過程步驟，如圖3所示：

圖3

2、公式 =COUNTIF(D2:D7,">"&AVERAGE(D2:D7)) 中的條件為 ">"&AVERAGE(D2:D7)，條件中的 AVERAGE(D2:D7) 用于求 D2:D7 的平均值（結果為 1790.5），然后用 & 把大于號跟它連起來組成條件，即 ">1790.5"。

（四）條件中有通配符 ? 和 * 的實例

1、假如要統計姓李的員工個數。雙擊 D2 單元格，把公式 =COUNTIF(A2:A10,"李??") 復制到 D2，按回車，返回統計結果 2；雙擊 D2，把公式中的 ?? 改為 *，按回車，也返回統計結果 2；操作過程步驟，如圖4所示：

圖4

2、公式 =COUNTIF(A2:A10,"李??") 中的條件為 "李??"，意思是“以李字開頭且李字后僅有兩個字”；當把條件改為"李*"后，意思是“以李字開頭即可”，李字后可以有一個或多個任意字符。條件中有通配符 ? 和 * 的情況在前面的篇章中已介紹多次，如果想了解更多的查找方式，可以參考《Excel HLookUp函數的使用方法，包含首行需排序、返回#N/A及與Match組合/非首行實例》一文。

3、條件為 * 和 ?* 的實例

A、條件為 * 的實例。雙擊 E10 單元格，把公式 =COUNTIF(E1:E9,"*") 復制到 E10，按回車，返回統計結果 2；雙擊 E9，把返回空（""）的公式去掉，按回車，統計結果變為 1；再把 E1 中的文字刪除，統計結果變為 0，演示如圖5所示：

圖5

B、條件為 ?* 的實例。雙擊 E10 單元格，把公式 =COUNTIF(E1:E9,"?*") 復制到 E10，按回車，返回統計結果 1；雙擊 E9，把 "" 改為" "，統計結果變為 2；再把 E1 中的文字刪除，統計結果變為 1，演示如圖6所示：

圖6

* 和 ?* 的區別：* 可以是一個或多字符（包括空值""），?* 至少要有兩個字或空值""，演示中已經說明這兩個問題。

（五）幾個特殊的實例

1、雙擊 B11 單元格，把公式 =COUNTIF(B2:B10,">=!") 復制到 B11，按回車，返回統計結果 7；雙擊 B10，它是一個空單元格，再雙擊 B9，里面有一個公式，雙擊 B7，按空格鍵在“財務部”前加一個空格，按回車，統計結果變為 7；把公式 =COUNTIF(B2:B10,"><") 復制到 B12，按回車，返回統計結果 6，雙擊 B7，把“財務部”前的空格刪除，B11 和 B12 中的統計結果都變為 7；雙擊 B13，把公式 =COUNTIF(B2:B10,">""") 復制到 B13，按回車，返回統計結果 7，雙擊 B7，再次在“財務部”前加一個空格，B11、B12 和 B13 的統計結果都變為 7；雙擊 C11，把公式 =COUNTIF(B2:B10,"<>") 復制到 C11，按回車，返回統計結果 8；操作過程步驟，如圖7所示：

圖7

2、公式說明：

公式 =COUNTIF(B2:B10,">=!")、 =COUNTIF(B2:B10,"><") 和 =COUNTIF(B2:B10,">""") 的統計結果一樣，它們都不包含有公式的單元格、空單元格和文字前有空格的單元格；而公式 =COUNTIF(B2:B10,"<>") 包含有公式的單元格，不包含空單元格。

三、Excel CountIf函數的擴展使用實例

（一）Sum + If + CountIf函數組合實現統計不為空且不包含重復值的個數

1、雙擊 B9 單元格，把公式 =SUM(IF(B2:B8<>"",1/COUNTIF(B2:B8,B2:B8))) 復制到 B9，按 Ctrl + Shift + 回車，返回統計結果 3，操作過程步驟，如圖8所示：

圖8

2、公式 =SUM(IF(B2:B8<>"",1/COUNTIF(B2:B8,B2:B8))) 說明：

A、B2:B8 用于以數組形式返回 B2 到 B8 中的所有值，即 {"財務部";"銷售部";"行政部";"銷售部";"行政部";"財務部";"行政部"}。

B、COUNTIF(B2:B8,B2:B8) 執行時，第一次從 B2:B8 中取出 B2（即“財務部”），然后統計 B2 在 B2:B8 中的個數，結果為 2；第二次統計 B3 在 B2:B8 中的個數，結果為 2；其它的以此類推，最后返回 {2;2;3;2;3;2;3}。

C、1/COUNTIF(B2:B8,B2:B8) 變為 1/{2;2;3;2;3;2;3}，接著用 1 除以數組中的每一個元素，結果返回{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333}；這一步的作用是把重復值的總數看為 1，例如重復次為 2 的，每個 1/2 等于 0.5，進一步計算再把兩個 0.5 加起來，恰好是 1，即去掉一個重復項。

D、B2:B8<>"" 意思是：每次從 B2:B8 中取出一個元素，如果不為空，返回 True，否則返回 False；最后返回{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}。

E、則公式變為 =SUM(IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333}))，接著從 If 的條件數組中取出第一個元素 TRUE，由于為真，所以返回第二個數組中對應的元素 0.5；直到取完條件數組中的每個，最后返回{0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333}；則公式變為 =SUM({0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5;0.333333333333333})，最后用 Sum 對數組中的每個元素求和，結果為 3。

（二）OffSet + CountIf函數組合使用實現分段統計

OffSet + CountIf 實現分段統計請查看《Excel OffSet函數的使用方法，含與Sum、Match、CountIf、If、Or、Row組合實現動態求和、一個重復多個、分段統計和斷碼檢查》一文。

（三）CountIf函數多條件統計

1、假如要統計銷售額在 5 萬到 8 萬的銷售員個數。雙擊 C9 單元格，把公式 =SUM(COUNTIF(C2:C8,{">=50000",">80000"})*{1,-1}) 復制到 C9，按回車，返回統計結果 3，操作過程步驟，如圖9所示：

圖9

2、公式 =SUM(COUNTIF(C2:C8,{">=50000",">80000"})*{1,-1}) 說明：

A、C2:C8 為 CountIf 的統計范圍，{">=50000",">80000"} 為條件，該條件為數組，共包含兩個條件，第一個條件為 ">=50000"，第二個條件為 ">80000"，意思是：先在 C2:C8 中統計出大于等于 50000 的個數，結果為 4；再統計出大于 80000 的個數，結果為 1。

B、則公式變為 =SUM({4,1}*{1,-1})，接著，把兩個數組的對應元素相乘，公式變為 =SUM({4,-1})，最后對數組求和，結果為 3。這個方法的關鍵為：先統計出所有大于等于 5 萬的個數，再統計出大于 8 萬的個數，然后用前者減后者。

3、統計其它范圍的公式分別為：

A、大于 50000 且小于 80000：=SUM(COUNTIF(C2:C8,{">50000",">=80000"})*{1,-1})

B、大于等于 50000 且小于 80000：=SUM(COUNTIF(C2:C8,{">=50000",">=80000"})*{1,-1}) 或 =SUM(COUNTIF(C2:C8,">="&{50000,80000})*{1,-1})

C、大于 50000 且小于等于 80000：=SUM(COUNTIF(C2:C8,{">50000",">80000"})*{1,-1}) 或 =COUNTIF(C2:C8,">50000"）- COUNTIF(C2:C8,">80000"）

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。