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))こちらのほうが読みやすい場面が多いので、まずこちらを検討してください。
作業列を作らずに済ませる
単価と数量から売上を求める場合、通常は作業列を作ります。各行で掛け算をして、最後にその列を合計する流れです。この方法は分かりやすく、途中の値も確認できるので、実務では十分に有効です。
しかし、作業列を置きたくない場面もあります。表のレイアウトが決まっている、他の人が使うので列を増やしたくない、集計だけを別の場所に出したい。こういう場合に、掛けてから足す関数が使えます。
範囲を二つ渡せば、対応する行同士を掛けて、その合計を返します。作業列は不要です。範囲は三つ以上でも構いません。
注意点として、渡す範囲はすべて同じ行数でなければなりません。片方だけ広げると、エラーになるか、意図しない結果になります。
また、範囲に文字列が含まれていると、掛け算ができずにエラーになる場合があります。空白は零として扱われるので問題ありませんが、文字列が混ざっていないかは確認してください。
条件付きの集計として使う
この関数の本当の価値は、条件付きの集計にあります。条件式を掛け合わせることで、複雑な条件の集計が書けます。
仕組みは、真偽値が内部的に一と零として扱われることを利用しています。条件式を書くと、行ごとに真偽値の並びができます。それを掛け合わせれば、すべての条件を満たす行だけが一になります。さらに集計対象の範囲を掛ければ、条件に合う行の値だけが残り、合計されます。
条件付き集計の専用関数があるのに、なぜこの方法が必要なのか。理由は三つあります。
第一に、専用関数では書けない条件があるためです。条件範囲に対して関数を適用したい場合、専用関数は受け付けません。年を取り出して比較する、文字数で判定する、といった条件はこの方法でしか書けません。
第二に、または条件を扱えるためです。条件を足し合わせれば、どちらかを満たす行が対象になります。
第三に、他の範囲との掛け算を同時に行えるためです。条件に合う行の、単価かける数量の合計、というような集計が一つの数式で書けます。
条件式の書き方に慣れる
条件式の部分は、範囲と値の比較として書きます。ある列全体を特定の値と比べると、行ごとの真偽値が並びます。
複数の条件をかつで結ぶなら掛け算、またはで結ぶなら足し算です。この対応を覚えておけば、条件の組み立てで迷いません。
またはで結ぶ場合は注意が必要です。両方を満たす行では、足した結果が二になります。そのまま集計対象と掛けると、二重に計上されます。ゼロより大きいかを判定して真偽値に戻すか、条件が排他的であることを確認してください。
期間で絞る場合は、開始日以上という条件と終了日以下という条件を掛け合わせます。日付はセルに置いて参照する形が確実です。
条件式に関数を使う場合、その関数が範囲を受け付けるかどうかで挙動が変わります。範囲をそのまま渡せる関数と、一つの値しか受け付けない関数があります。後者を使うと、意図した並びになりません。動作を確認してから組み込んでください。
件数を数える使い方
集計対象の範囲を渡さずに、条件式だけを掛け合わせると、条件を満たす行の件数が返ります。真偽値の一を合計する形になるためです。
これは、条件付きの件数関数では書けない条件を数えたいときに使えます。日付から年を取り出して比較する、文字列の長さで判定する、といった条件です。
また、または条件の件数も、足し算で書けます。ただし重複の扱いには注意してください。
件数と合計を同じ条件で出したい場合、条件式の部分は共通なので、片方をコピーして集計対象を足し引きするだけで済みます。集計表を組み立てる際の手間が減ります。
なお、単純な条件であれば、専用の関数のほうが読みやすくなります。この方法を使うのは、専用関数では書けない条件に限るのが実務的です。
加重平均を実務で使う
単位あたりの値を平均する場面では、加重平均が正解になることがほとんどです。掛けてから足す関数を使えば、一つの数式で書けます。
平均単価を求める場合、単価と数量を掛けた合計を、数量の合計で割ります。店舗ごとの平均単価をさらに平均する方法とは、結果が変わります。販売数の違いが反映されるためです。
達成率の平均も同様です。目標と実績をそれぞれ合計してから割ります。月ごとの達成率を平均すると、規模の違う月が同じ重みで扱われます。
利益率、稼働率、歩留まり。いずれも比率なので、単純平均では実態を表しません。分子と分母をそれぞれ合計してから割り直します。
条件付きの加重平均も書けます。条件式を掛け合わせた上で、値と重みを掛けます。特定の分類だけの加重平均、といった集計ができます。
この違いを知らずに単純平均を報告していると、指摘されたときに説明できません。比率を平均する場面では、必ず重みを考えてください。
二次元の集計に使う
掛けてから足す関数は、行と列の両方に条件がある集計にも使えます。
たとえば、行に商品、列に月が並んだ表があり、特定の商品群の特定の期間だけを合計したい場合。行の条件と列の条件を、それぞれ真偽値の並びとして作り、掛け合わせます。
行の条件は縦に並び、列の条件は横に並びます。この二つを掛けると、行と列の交差する部分だけが一になる二次元の並びができます。これに集計対象の範囲を掛ければ、該当する部分だけが合計されます。
条件付きの集計関数では、この形の集計はできません。条件範囲と集計範囲が同じ形である必要があるためです。
やや高度な使い方ですが、横持ちの表を扱わざるを得ない場面では有効です。データの持ち方を変えられるなら、縦持ちにして条件付きの集計関数を使うほうが読みやすくなります。
いずれにせよ、この関数の本質は「並びを掛け合わせて合計する」ことです。この一点を理解していれば、応用は自分で組み立てられます。
よくある質問
Q. エラーになります
範囲の行数が揃っていないか、文字列が混ざっています。範囲を確認してください。
Q. 条件付き集計の専用関数とどちらを使うべきですか
単純な条件なら専用関数のほうが読みやすくなります。専用関数で書けない条件のときだけ、この方法を使ってください。
Q. または条件で数が合いません
両方を満たす行が二重に数えられています。ゼロより大きいかを判定して真偽値に戻してください。
Q. 文字列が混ざるとエラーになります
掛け算ができないためです。条件式で除外するか、対象範囲を数値だけにしてください。
Q. 範囲は何個まで渡せますか
実用上、困らない数を渡せます。ただし多いと読みにくくなります。
Q. 空白セルはどう扱われますか
零として扱われます。合計には影響しません。
Q. 加重平均を出せますか
出せます。値と重みを掛けた合計を、重みの合計で割ってください。
Q. 重くなることはありますか
範囲を列全体で指定すると負荷がかかります。データのある範囲に絞ってください。
まとめ
- 書式は
=SUMPRODUCT(範囲1, 範囲2)。掛けて足す - 掛け算が AND、足し算が OR になる
- SUMIFS で書けるなら、SUMIFSのほうが読みやすく軽い
- 出番は「条件に計算が入る」「加重平均」など、SUMIFSで書けない場合
- 範囲の大きさは必ず揃える
- スプレッドシートなら FILTER や QUERY で代替できることが多い