Excel兩個以上條件統計CountIfs函數的使用方法，含與Sum組合統計滿足And和Or條件的個數

在 excel 中，CountIfs函數用于同時滿足兩個以上條件的個數統計，CountIf函數用于滿足一個條件的個數統計。CountIfs函數參數由條件區域/條件對組成，至少要有一個條件區域/條件對，最多只能有 127 條件區域/條件對。

大多情況下，CountIfs函數本身就可以完成滿足多個條件的統計，但它自身無法完成“或關系”（Or 條件）的統計，如果要實現此功能，需要與Sum函數組合；另外，如果要實現區域不固定的個數統計，需要用 CountIfs + OffSet。

一、Excel CountIfs函數語法

1、表達式：COUNTIFS(Criteria_Range1, Criteria1, [Criteria_Range2, Criteria2], …)

中文表達式：COUNTIFS(條件區域1, 條件1, [條件區域2, 條件2], …)

2、說明：

A、一個條件區域和一個條件組成一個CountIfs函數的條件區域/條件對，至少要有一個條件區域/條件對，最多只能有 127 個條件區域/條件對；每個條件之間是“與”的關系，假如有兩個條件，則要求同時滿足兩個條件。

B、條件可以是數字、文本（如："員工"）、單元格引用、表達式（如：">=100"），且條件中可以有函數(如："<="&Average(A2:A8)）。

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

D、如果同時引用多個條件區域，每個條件區域的行數和列數必須相同，但它們不必相鄰，否則返回值錯誤 #VALUE!。

C、如果條件是對空單元格的引用，CountIfs函數將返回 0。

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

（一）條件為文本且只有一個條件區域/條件對的實例

1、假如要統計銷售業績表中業績評定為優異的員工個數。雙擊 D9 單元格，把公式 =COUNTIFS(D2:D8,"優異") 復制到 D9，按回車，返回統計結果 2；操作過程步驟，如圖1所示：

圖1

2、公式說明：

在 =COUNTIFS(D2:D8,"優異") 中，D2:D8 為條件區域，"優異"為條件，意思是：在 D2 至 D8 中統計“業績評定為優異”的個數；一般情況下，一個條件常常用CountIf函數，兩個以上條件用CountIfs函數。

（二）條件為表達式和有函數且有兩個條件區域/條件對的實例

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

圖2

2、公式 =COUNTIFS(C2:C7,">3",D2:D7,">="&AVERAGE(D2:D7)) 說明：

第一個條件區域/條件為 C2:C7,">3"，用于在 C2:C7 找出所有價格大于 3 的數值；第二個條件區域/條件對為 D2:D7,">="&AVERAGE(D2:D7)，用于在 D2:D7 中找出所有銷量大于等于平均值的數值，AVERAGE(D2:D7) 用于求銷量的平均值，連接運算符 & 用于把 ">=" 與平均值連接起來。最后篩選出既滿足第一個條件又滿足第二個條件的數值作為統計結果。

（三）條件是對空單元格的引用，CountIfs函數將返回 0 的實例

雙擊 C9 單元格，把公式 =COUNTIFS(C2:C8,A8) 復制到 C9，按回車，返回統計結果 0；選中 C9，按住 Alt，分別按一次 M 和 V，打開“公式求值”窗口，單擊“求值”或按回車，A8 返回 0，說明條件為空單元格時，CountIfs函數將它的值視為 0；操作過程步驟，如圖3所示：

圖3

（四）條件為空與不為空的實例

1、假如要統計服裝尺碼表中，尺碼 S 不為空且 L 為空的個數。雙擊 B11 單元格，把公式 =COUNTIFS(B2:B10,"<>",D2:D10,"") 復制到B11，按回車，返回統計結果 2；操作過程步驟，如圖4所示：

圖4

2、公式=COUNTIFS(B2:B10,"<>",D2:D10,"") 說明：

公式中第一個條件 "<>" 表示不為空，也可以表示為 "<>"&""；第二個條件 "" 表示為空。

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

1、假如要統計服裝名稱同時滿足以“粉紅”開頭、以“襯衫”結尾且僅由四個字組成的個數。雙擊 B9 單元格，把公式 =COUNTIFS(B2:B8,"粉紅*",B2:B8,"*襯衫",B2:B8,"????") 復制到 B9，按回車，返回統計結果 1；操作過程步驟，如圖5所示：

圖5

2、公式 =COUNTIFS(B2:B8,"粉紅*",B2:B8,"*襯衫",B2:B8,"????") 說明：

公式中的三個條件區域都為 B2:B8；條件 "粉紅*" 意思是以“粉紅”開頭，* 表示任意一個或多個字符；條件 "*襯衫" 表示以任意一個或多個字符開頭但要以“襯衫”結尾；條件 "????" 表示僅由四個字符組成。

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

（一）CountIf + OffSet函數組合返回錯誤的原因

1、雙擊 A10 單元格，把公式 =COUNTIFS(OFFSET($A$1,1,1,6,1),">600",OFFSET($A$1,2,2,7,1),">600") 復制到 A10，按回車，返回值錯誤 #VALUE!，選中 A10，按住 Alt，依次按一次 M 和 V，打開“公式求值”窗口，按回車“求值”，直到公式變為 COUNTIFS($B$2:$B$7,">600",$C$3:$C$9,">600")；關閉“公式求值”窗口，雙擊 B10，把公式改為 =COUNTIFS(OFFSET($A$1,1,1,8,1),">600",OFFSET($A$1,1,2,8,1),">600")，按回車，返回統計結果 3，同樣方法打開“公式求值”窗口，按回車求值直到公式變為 COUNTIFS($B$2:$B$9,">600",$C$2:$C$9,">600")；操作過程步驟，如圖6所示：

圖6

2、公式說明：

A、公式中 OFFSET($A$1,1,1,6,1) 用于返回 A1 下 1 行 1 列且高度為 6、寬度為 1 的單元格引用，即返回對 B2:B7 的引用；$A$1 表示對 A1 的絕對引用，無論往下拖還是往右拖，A1 始終不會變。

B、OFFSET($A$1,2,2,7,1) 用于返回 A1 下 2 行 2 列且高度為 7、寬度為 1 的單元格引用，即返回對 C3:C9 的引用。

C、則公式變為 =COUNTIFS($B$2:$B$7,">600",$C$3:$C$9,">600")，條件區域 $B$2:$B$7 與 $C$3:$C$9 的行數不相同，而CountIfs函數要求條件區域的行數和列數相同，因此公式返回值錯誤 #VALUE!。

D、當把公式改為 =COUNTIFS(OFFSET($A$1,1,1,8,1),">600",OFFSET($A$1,1,2,8,1),">600") 后，兩個 OffSet 返回的行數和列數相同（即都為 $B$2:$B$9），因此能返回正確的統計結果。

（二）Sum + CountIfs函數組合實現同時滿足 And 和 Or 條件的統計

1、假如要統計行政部和財務部的員工總數。雙擊 B9 單元格，把公式 =SUM(COUNTIFS(C2:C8,"員工",B2:B8,{"行政部","財務部"})) 復制到 B9，按回車，返回統計結果 3，操作過程步驟，如圖7所示：

圖7

2、公式 =SUM(COUNTIFS(C2:C8,"員工",B2:B8,{"行政部","財務部"})) 說明：

A、公式第二個條件為數組 {"行政部","財務部"}，意思是在 B2:B8 中統計“行政部和財務部”的員工；執行時，先統計出“行政部”的員工個數（結果為 2），再統計出“財務部”的員工個數（結果為 1），CountIfs 最后返回數組 {2,1}。

B、則公式變為 =SUM({2,1})，最后用 Sum 對數組求和，結果為 3；如果不用 Sum，只返回滿足數組 {"行政部","財務部"} 中第一個條件的統計結果。

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