SUMPRODUCT函數介紹—舊陣列函數時代的英雄
SUMPRODUCT函數可算是Excel進階函數之一，它在Excel動態陣列公式功能出現之前，可說是處理陣列公式最厲害的函數，有非常多的應用方式及使用技巧。本篇文章將先介紹SUMPRODUCT函數的幾個應用方式，接著再說明新的動態陣列公式功能釋出後，有哪些可替代SUMPRODUCT函數的方式。
SUMPRODUCT函數使用目的
於SUMPRODUCT這個名字中，sum是加總之意，product有相乘之意；另SUMPRODUCT函數的參數為陣列(可放入多個陣列)：=SUMPRODUCT(array1,[array2],[array3],...)，綜上所述，此函數的使用目的為：
將不同陣列先進行相乘，再進行加總。
「將不同陣列先進行相乘，再進行加總」這句話是什麼意思呢？可能有些人對「陣列」的觀念不夠清楚，那可以先想像為「值」的相乘(1×1的陣列就是值)，實際用SUMPRODUCT函數舉個例：
=SUMPRODUCT(2,3) = 2×3 = 6
接著進一步用「陣列」來舉例：
=SUMPRODUCT({2,1},{3,5}) = 2×3+1×5 = 11
下一節，將以實際常用的範例進行說明。
SUMPRODUCT函數應用舉例
1.以單價、數量算出總價
Excel用戶若要以商品的單價及銷售量算出總銷售額，一般會先計算每一個商品的銷售額，再進行加總。(如下圖)
若利用SUMPRODUCT函數，總銷售額的公式可簡化為：
SUMPRODUCT(C2:C6,D2:D6) = 1×5+2×4+3×3+4×2+5×1 = 35
2.條件式加總
下表為一旅遊人數統計表，假設要計算出台北的總旅遊人數，那計算的條件為：「城市」欄位="台北"。
此條件式套用在SUMPRODUCT函數中，其輸出的值為一True/False的陣列。(如下圖公式)
因為True=1、False=0，所以我們期望得到的結果是：
SUMPRODUCT({109893;161175;147539;184384;342297...},{1;1;1;1;0;...}) = 109893×1+161175×1+147539×1+184384×1+342297×0.... = 602991。
但此公式的結果卻為：0。(如下圖)
其實原因是SUMPRODUCT函數中的參數不接受True/False的形式，故有一個小技巧，可以先將此True/False的陣列轉換為1或0的陣列，可直接在其括號外面×1。(如下圖)
就可以得到正確的結果喔。(如下圖)
也可以利用此方法去進行多個條件的計算，如城市="台北"及季節="春"。(如下圖)
如何以SUM的動態陣列公式替代SUMPRODUCT函數
在過去動態陣列公式功能還沒出現以前，SUM這個函數僅有最簡單的相加功能：將一串數值(一陣列)進行相加。
但在動態陣列公式功能出現後，可藉由此功能去改變SUM函數公式裡的陣列，再進行相加。以第二節第一個例子來講，公式可以寫為：
=SUM(C2:C6*D2:D6) = SUM(1×5+2×4+3×3+4×2+5×1) = SUM(5,8,9,8,5) = 35 (如下圖)
這是舊版Excel無法辦到的方式呢！
接著以第二節第二個例子來講，也可以藉由條件式True/False的陣列去改變SUM函數中既有的陣列，如公式可寫成：
SUM(旅遊人數表[旅遊人數]*(旅遊人數表[城市]="台北")) = {109893;161175;147539;184384;342297...},{TRUE;TRUE;TRUE;TRUE;FALSE;...}) = 109893×1+161175×1+147539×1+184384×1+342297×0.... = 602991。
是不是很有趣呢！
另外若各位想了解更多的SUMPRODUCT函數應用，可以參考Excel專家Alan Murray先生近期於LinkedIn上發表的一篇文章。
總結及補充：
上述舉的兩個範例皆為加總，其實SUMPRODUCT函數也可以用來計數，用法為SUMPRODUCT((範圍="條件")*1)，其原理是將True/False的陣列轉化為1或0的陣列，再進行加總計數，於上方Alan Murray先生的文章中有相關的範例可參考喔。
雖然在動態陣列公式功能出現以後，SUMPRODUCT函數的使用頻率可能會逐漸降低，甚至慢慢被人們遺忘，但SUMPRODUCT函數絕對是在舊Excel時代中無疑是扮演最重要的角色之一，仍是Excel用戶相當值得花時間了解、學習的一個函數