Excel 多條件變數 countif 函數 sumproduct and or

最近在工作中，仍然繼續使用 Excel SUMPRODUCT 函數，發現其他 SUMPRODUCT 的進階使用，包括：在 SUMPRODUCT 內使用 and、or。所以我改寫之前廣受歡迎的《Excel多條件(變數)countif函數sumproduct》一文。

簡單地說，SUMPRODUCT 就是進階版的「2 variable countif」，所以不但能 count 超過二個以上的條件（變數），還可以使用 or、and 的條件。

另外，在這篇文章，我也同時介紹如何在 Google Spreadsheet 內使用 SUMPRODUCT。

在 SUMPRODUCT 內使用 and

以下面的例子來說：


如果要算出「E2」欄位：(Sales部門內) and (進度等於 A)的人數，就要輸入：
=SUMPRODUCT((B2:B11="Sales")*(C2:C11="A"))


SUMPRODUCT 內的「*」就是 and 的意思。

在 SUMPRODUCT 內使用 or

以前面的例子來說：(R&D 部門內) and ((進度等於 A) 或(進度等於 B)) 的人數，就可以改成輸入：
=SUMPRODUCT((B2:B11="R&D")*((C2:C11="A")+(C2:C11="B"))

SUMPRODUCT 內的「+」就是 or 的意思。

將 SUMPRODUCT True/False 陣列轉換為 1/0 陣列

在看了「火星人的天空之城」網站後，我發現 SUMPRODUCT 原來有另一種表達方式：1/0 陣列。

舉前面算出「E2」欄位：(Sales部門內) and (進度等於 A)的人數，可以改成：
=SUMPRODUCT(--(B2:B11="Sales"),--(C2:C11="A"))

再以前面的例子來說：(R&D 部門內) and ((進度等於 A) 或(進度等於 B)) 的人數，就可以改成：
=SUMPRODUCT(--(B2:B11="R&D"),--((C2:C11="A")+(C2:C11="B"))

此處的「--」是為了將 True/False 陣列轉換為 1/0 陣列。

在 Google Spreadsheet 內使用 SUMPRODUCT

如果你想要在 Google Spreadsheet 內使用 SUMPRODUCT 這個函數，必須在前面加入ARRAYFORMULA函數，就能正常使用了。例如：
=ARRAYFORMULA(SUMPRODUCT((B2:B11="Sales")*(C2:C11="A")))

注意事項

在使用 SUMPRODUCT 函數時，要注意" "內不可以有多餘的空白。如果寫成"Sales "就會得到 #VALUE! 錯誤訊息。

1. mrexcel -《COUNTIF with two Variables?》
   
2. Google 網上論壇-《SUMPRODUCT won't work》
3. 火星人的天空之城-《Excel-在SUMPRODUCT函數中執行OR運算 》

Jose’s Google+ profile