SUMPRODUCTは、対応する要素を掛け合わせて、その合計を返す関数です。
「単価 × 数量」を全行分まとめて合計する、というのが本来の用途です。ただし実務では、SUMIFS では書けない条件の集計に使われることのほうが多くなります。
書式
=SUMPRODUCT(範囲1, [範囲2], ...)範囲はすべて同じ大きさである必要があります。
基本の動き
| A | B | |
|---|---|---|
| 1 | 単価 | 数量 |
| 2 | 100 | 3 |
| 3 | 200 | 2 |
| 4 | 150 | 4 |
=SUMPRODUCT(A2:A4, B2:B4)結果：1300（100×3 + 200×2 + 150×4）
作業列に「金額」を作って SUM する必要がありません。列を1つ減らせます。
掛け算がANDになる仕組み
ここがSUMPRODUCTの本領です。
TRUE は1、FALSE は0として扱われます。この性質を使うと、条件判定を掛け算で表現できます。
=SUMPRODUCT((A2:A100="東京") * (B2:B100>=1000))- – 両方
TRUE→ 1 × 1 = 1 - – 片方
FALSE→ 1 × 0 = 0
つまり掛け算が ANDになり、合計が「条件に合う件数」になります。
金額を合計するなら、値の範囲を掛けます。
=SUMPRODUCT((A2:A100="東京") * (B2:B100>=1000) * C2:C100)条件に合う行だけ 1 × 金額、合わない行は 0 × 金額 = 0 になり、合計されます。
同様に、足し算が OR になります。
=SUMPRODUCT(((A2:A100="東京") + (A2:A100="大阪")) * C2:C100)括弧を忘れないでください。 + より * が先に計算されるため、括弧がないと意図が変わります。
SUMIFSで書けるなら、そちらを使う
上の例は SUMIFS でも書けます。
=SUMIFS(C:C, A:A, "東京", B:B, ">=1000")読みやすさでは SUMIFS が上です。 処理も軽い。書けるならSUMIFSを使ってください。
SUMPRODUCTを使うべきなのは、SUMIFSでは書けない場合だけです。
SUMIFSでは書けない集計
1. 条件に計算が入る
「単価×数量が1000以上の行」のように、条件式の中に計算がある場合。
=SUMPRODUCT((A2:A100 * B2:B100 >= 1000) * 1)SUMIFS の条件は文字列で書くため、こうした計算を含められません。
2. 月ごとの集計を関数で導く
=SUMPRODUCT((MONTH(A2:A100)=4) * C2:C100)日付から月を取り出して判定しています。SUMIFS では期間の上限下限で表現する必要がありますが、SUMPRODUCTなら直接書けます。
3. 重複を除いた件数
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))同じ値がn個あれば 1/n がn回足されて1になる、という仕組みです。
範囲に空欄があると #DIV/0! になります。 空欄のない範囲に限定するか、次のように書きます。
=SUMPRODUCT((A2:A100<>"") / COUNTIF(A2:A100, A2:A100 & ""))スプレッドシートなら UNIQUE のほうが簡単です。
=COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>"")))4. 部分一致での集計
=SUMPRODUCT(ISNUMBER(SEARCH("営業", B2:B100)) * C2:C100)SUMIF のワイルドカードでも書けますが、複数の条件と組み合わせるならこちらが素直です。
5. 加重平均
=SUMPRODUCT(A2:A100, B2:B100) / SUM(B2:B100)「単価 × 数量の合計 ÷ 数量の合計」＝加重平均単価です。AVERAGE では出せません。
注意点
範囲の大きさを揃える
すべての範囲が同じ行数でなければ #VALUE! になります。列全体（A:A）と A2:A100 を混ぜないでください。
文字列が混ざると #VALUE!
掛け算の対象に文字列があるとエラーになります。条件式で包むか、-- で数値化してください。
=SUMPRODUCT(--(A2:A100="東京"))-- は「マイナスを2回かける」ことで TRUE/FALSE を1/0に変換する定番の書き方です。* 1 でも同じです。
動作が重い
SUMPRODUCTは範囲全体を計算します。列全体を指定して何本も置くと重くなります。範囲を実際に使う分だけに限定してください。
Excelとの違い
書式・挙動ともに共通です。-- による数値化も同じように使えます。
Excelでは古くから「配列数式の代わり」としてSUMPRODUCTが多用されてきましたが、現在は SUMIFS や FILTER で書けることが増えています。
スプレッドシートでは QUERY FILTER ARRAYFORMULA があるため、SUMPRODUCTの出番はさらに少なくなります。
=SUM(FILTER(C2:C100, A2:A100="東京", B2:B100>=1000))こちらのほうが読みやすい場面が多いので、まずこちらを検討してください。
まとめ
- – 書式は
=SUMPRODUCT(範囲1, 範囲2)。掛けて足す - – 掛け算が AND、足し算が OR になる
- – SUMIFS で書けるなら、SUMIFSのほうが読みやすく軽い
- – 出番は「条件に計算が入る」「加重平均」など、SUMIFSで書けない場合
- – 範囲の大きさは必ず揃える
- – スプレッドシートなら FILTER や QUERY で代替できることが多い
コメントを残す