錦子老師

今天和大家分享一個名字，但是應用非常廣泛的函數——SUMPRODUCT函數。

開門見山，咱們通過幾個範例，一起來看下SUMPRODUCT函數具體能幹什麼樣的工作，本單元通過幾個實例詳細解說比SUMIF好用十倍的函數——SUMPRODUCT函數的使用方法及實例。

假設上面這張圖，是某個公司工資發放的部分記錄表，A欄是工資發放的時間，B欄是員工所屬的部門，C欄是員工姓名，D欄是相關員工領取的工資金額。

那麼，現在問題來了：

n  西門慶同志領取了幾次工資?

這是一個單條件計數的問題，通常我們使用COUNTIF函數：

點取F2儲存格輸入公式：「=COUNTIF(C2:C16,"西門慶")」後，按Enter鍵。

如果使用SUMPRODUCT函數，一般寫成這樣：

點取F2儲存格輸入公式：「=SUMPRODUCT((C2:C16="西門慶")*1)」後，按Enter鍵，結果3次，如下圖所示。

先判斷C2:C16的值是否等於「西門慶」，相等則傳回TRUE，不等則傳回FALSE，由此建立一個有邏輯值構成的記憶體陣列，SUMPRODUCT有一個特性，它會將非數值型的陣列元素作為0處理，邏輯值自然是屬於非數值型的陣列元素，我們使用*1的方式，把邏輯值轉化為數值，TRUE轉化為1，FALSE轉化為0，最後統計求和。

n  西門慶同志一共領取了多少工資?

這是一個單條件求和的問題,通常我們使用SUMIF函數：

點取F5儲存格輸入公式：「=SUMIF(C2:C16,"西門慶",D2:D16)」後，按Enter鍵。

如果使用SUMPRODUCT函數，我們可以寫成這樣：

點取F5儲存格輸入公式：「=SUMPRODUCT((C2:C16="西門慶")*D2:D16)」後，按Enter鍵，結果78,339，如下圖所示。

依然首先判斷C2:C16的值是否等於「西門慶」，得到邏輯值FALSE或TRUE，再和D2:D16的值對應相乘，最後統計求和得出結果。

看完了上面兩個問題，有些使用者心裡就嘀咕了，貌似SUMPRODUCT能幹的事，SUMIF和COUNTIF也能做到，而且做的更好，那麼還要SUMPRODUCT幹啥?

鄉親們呐，話不能這麼說，SUMPRODUCT可是上得廳堂下得廚房，對工作環境不挑不揀，它對參數類型沒有啥特別要求，COUNTIF和SUMIF就不同咧，他倆要求個別參數，必須是範圍陣列(Range)，比如下面這兩個問題，COUNTIF和SUMIF就犯難了。

n  三月份業務部發放了幾次工資?累計發放了多少?

三月份業務部發放了幾次工資，這是一個多條件計數的問題呢：

第一個條件，發放工資的時間必須是三月份;

第二個條件，發放工資的部門必須是業務部。

如果使用多條件計數函數COUNTIFS，判斷發放工資的時間是否屬於三月份，會簡單問題複雜化。

而使用SUMPRODUCT函數，咱們可以簡單利索的寫成這樣：

點取F8儲存格輸入公式：「=SUMPRODUCT((MONTH(A2:A16)=3)*(B2:B16="業務部"))」後，按Enter鍵，計算出三月份業務部發放薪資共有2筆，如下圖所示。

統計三月份業務部發放了多少工資?

鄉親們都曉得，這是一個常見的多條件求總和問題。如果使用SUMIFS函數，判斷發放工資的時間是否屬於三月份，和COUNTIFS類似，也會簡單問題複雜化。

SUMPRODUCT躍然而至：

點取F8儲存格輸入公式：「=SUMPRODUCT((MONTH(A2:A16)=6)*(B2:B16="財務部"), D2:D16)」後，按Enter鍵，計算出三月份業務部發放薪資共有45,102，如下圖所示。

上面這個公式可以說是SUMPRODUCT多條件求和的典型用法啦，可以歸納為:

=SUMPRODUCT((條件一)*(條件二)……,求和範圍)

n  三月份業務部和會計部合計發放了多少工資?

通過上面第三個問題，鄉親們都已經曉得如何計算三月份業務部發了多少工資，那麼三月份業務部和會計部合計發了多少工資，又當怎麼計算呢?

我們經常見有些表親把公式寫成這樣：

點取F11儲存格輸入公式：「=SUMPRODUCT((MONTH(A2:A16)=3)*(B2:B13="業務部")*D2:D16)+SUMPRODUCT((MONTH(A2:A16)=3)*(B2:B16="會計部")*D2:D16)」後，按Enter鍵，計算出三月份業務部與會計部總共發放薪資94,748，如下圖所示。

這些鄉親們估計心想，不就是計算兩個部門嗎?甭說兩個，二十個咱也能算，一個加一個，一直加到二十個，世上無難事，只怕有心人嘛……

公式寫的那麼長，先不談計算速度，首先它累手啊，其次萬一寫錯了，要修改，那也是麻煩死了。

其實我們可以寫成這樣：

點取F11儲存格輸入公式：「=SUMPRODUCT((MONTH(A2:A16)=3)*(B2:B16={"業務部","會計部"})*D2:D16)」後，按Enter鍵，計算出三月份業務部與會計部總共發放薪資94,748，如下圖所示。

部落格相關範例