SUMPRODUCT 用法：杯數乘單價一次加總，還能直接加條件

今天要來聊聊 SUMPRODUCT，它會把幾個範圍「對應的格子先相乘，再全部加起來」，最常見的用途就是杯數乘單價算營收。

這個函式在別篇已經順帶出現過幾次，這篇專門給想直接搜 SUMPRODUCT 的你，從最基本的寫法講到加條件。

沒有輔助欄的營收怎麼算

有一張手搖飲的訂單，8 筆，有日期、品項、通路、杯數跟單價，放在 A1:E9：

我想知道這 8 筆的總營收。手算的話，每列先乘：37 × 45 = 1665、112 × 45 = 5040，一路算到第 8 列，再把 8 個數字加起來，有點太麻煩了吧！

常見的做法是在 F 欄先放一個「金額」輔助欄，每列寫 =D2*E2，最後再 SUM。可是為了一個總數多一整欄，我個人不太想這樣做。這時候就可以把 SUMPRODUCT 派上場。

SUMPRODUCT

SUMPRODUCT 會把每個範圍同一個位置的格子相乘，再把所有乘積加總。

語法

=SUMPRODUCT(陣列1, [陣列2, ...])

• 陣列1：第一個範圍，例如杯數那一欄。
• 陣列2 以後：選填，要一起相乘的其他範圍。官方說明寫的是各範圍大小要相同。

第一個例子

杯數在 D2:D9、單價在 E2:E9，如果要用 SUMPRODUCT 寫，就是：

• D2:D9：杯數，第 1 個陣列。
• E2:E9：單價，第 2 個陣列，會和杯數同一列對應相乘。

先手算一下，8 列的乘積分別是 1665、5040、280、1440、2160、760、525、3630，加起來是 15500。來看看結果：

你應該會得到 15500！不用輔助欄，也不用 ARRAYFORMULA，SUMPRODUCT 本來就會一次處理整個範圍。這點在 ARRAYFORMULA 為什麼只算出一格 有提過，不用再多包一層。

只放一個範圍

只給一個範圍的話，就沒有東西可以乘，結果等於把範圍加總：

=SUMPRODUCT(D2:D9)

得到 329，也就是 8 筆的總杯數，和 =SUM(D2:D9) 一樣。看起來沒什麼用，不過下一節就要靠這個特性了。

加上條件

我想知道「外送」的營收。SUMPRODUCT 沒有條件參數，做法是自己做出一排「是不是外送」的 TRUE／FALSE，再乘進去：

• (C2:C9="外送")：把 C 欄 8 格各自跟「外送」比較，得到 8 個 TRUE 或 FALSE。
• *D2:D9*E2:E9：TRUE 乘起來當 1，FALSE 當 0，所以只有外送那幾列的杯數乘單價會留下來。

外送在第 3、5、6、8 列（試算表左邊的列號），乘積是 5040、1440、2160、525，加起來 9165：

這個寫法我會唸成「條件乘數字」。整個括號裡的比較要用括號包起來，不然乘法會先算，結果就不對了。

AND：多個條件相乘

兩個條件都要成立，就是兩段括號相乘。我想要「外送」而且是「蘋果紅茶」的營收：

只有第 6 列符合，48 × 45 = 2160：

OR：括號相加

「青茶或鮮奶茶」是兩個條件選一個，把兩段括號改成相加：

青茶有第 4、8 列（280、525），鮮奶茶是第 5 列（1440），合計 2245：

這裡有個小地方要注意。同一欄的兩個值不可能同時成立，所以相加只會是 0 或 1。可是如果 OR 的兩個條件在不同欄，同一列可能兩邊都成立，相加變成 2，那一列就被算了兩次，這個陷阱在 SUMIFS 進階陷阱 的 OR 條件那段講過，SUMPRODUCT 一樣會踩到。

條件可以是算出來的

SUMPRODUCT 的條件部分不限於「等於某個值」，括號裡可以放任何會回傳 TRUE／FALSE 的運算。我想算 10 月的營收，日期在 A 欄，用 MONTH 把月份取出來比：

10 月的有第 6 到 9 列：2160 + 760 + 525 + 3630 = 7075。MONTH 只看月份，不看年份，資料如果跨年，不同年的 10 月會被加在一起，這時要再加一個年份條件：

這個前提是 A 欄真的是日期，不是看起來像日期的文字，兩者的差別在 日期與時間的基礎。

和 SUMIFS 怎麼選

外送的杯數，兩種寫法都行：

• =SUMIFS(D2:D9, C2:C9, "外送")
• =SUMPRODUCT((C2:C9="外送")*D2:D9)

兩個都是 199（112 + 24 + 48 + 15）。這種「單純加總一欄」的題目，我會用 SUMIFS，短，而且 SUMIFS 進階陷阱 提到的萬用字元也能用。

SUMPRODUCT 在下面三種情況比較方便：

• 要加總的是兩欄「相乘後」的結果，例如杯數乘單價。SUMIFS 只能加總一個範圍，要先做輔助欄。
• 條件需要先運算過，像是 MONTH(A2:A9)=10、LEFT(B2:B9,2)="蘋果"。SUMIFS 的條件範圍只能直接給欄位。
• 想用 OR 把幾種情形加在一起，不想拆成好幾個 SUMIFS 再相加。

反過來，條件單純的時候，我還是偏好 SUMIFS。SUMPRODUCT 寫起來比較長，別人接手時要多讀一下才看得懂，SUMIFS 短又好讀。

SUMPRODUCT 的另外兩個常見用法在其他篇有例子：把機率乘上各情境的利潤算期望值，在 假設分析；用勾選值乘杯數算進度，在 核取方塊；加權平均單價則在 加權平均。

什麼時候會出錯

範圍大小不同

兩個範圍一個到第 9 列、一個只到第 8 列：

=SUMPRODUCT(D2:D9, E2:E8)

這會得到 #VALUE!，因為 8 格沒辦法跟 7 格一一對應。檢查一下兩個範圍的起訖列是不是一樣。

表頭被框進去

這是很常見的錯誤：範圍多框了第 1 列的表頭：

• 逗號分隔：=SUMPRODUCT(D1:D9, E1:E9)，表頭是文字，被當成 0，結果和原本一樣是 15500。
• 用 * 相乘：=SUMPRODUCT(D1:D9*E1:E9)，「杯數」乘「單價」兩個文字相乘，得到 #VALUE!。

兩種寫法對文字的反應不同，所以條件寫法（一定會用到 *）的範圍要從第 2 列開始。

動手練習

下次碰到「兩欄相乘再加總」的題目，先想想 SUMPRODUCT 能不能一條解決吧！我是喜特先生，Mr. Sheet，我們下個教學見！