SUMPRODUCT関数の使い方｜複数条件の集計を実例で解説

スポンサーリンク

「商品ごとの売上合計を出したいけど、単価と数量をいちいち掛け算してから足すのは面倒…」そんな経験、ありませんか？

数式が増えるほどミスも起きやすくなりますし、メンテナンスも大変ですよね。

そんなときに便利なのが、ExcelのSUMPRODUCT関数です。掛け算と合計をまとめて1つの数式で処理できるうえ、条件付きの集計にも使える万能な関数なんです。

この記事では、SUMPRODUCT関数の基本的な使い方から、実務で役立つ条件付き集計の応用例、よくあるエラーの対処法まで、実例付きでわかりやすく解説していきます。

SUMPRODUCT関数とは？

SUMPRODUCT関数（読み方: サムプロダクト関数）は、複数の配列やセル範囲の対応する要素を掛け算し、その合計を返す関数です。

名前の由来は「SUM（合計）」と「PRODUCT（積・掛け算）」の組み合わせ。文字どおり「掛け算の合計」を求める関数です。

たとえば、商品の「単価」と「数量」がそれぞれの列にあるとき、全商品の売上合計を一発で計算できます。通常なら「単価 x 数量」を1行ずつ計算してからSUM関数で合計する必要がありますが、SUMPRODUCT関数なら1つの数式で済みます。

SUMPRODUCT関数でできることをまとめると、次のとおりです。

  • 複数の範囲を要素ごとに掛け算して合計する
  • 条件に合うデータだけを集計する（条件付き集計）
  • 複数の条件を組み合わせた集計を行う
  • 作業列を使わずにスマートに計算できる

SUM関数で「合計」は得意だけど、「掛けてから合計」はできない…という場面で活躍する関数です。

SUMPRODUCT関数の書き方（構文と引数）

基本構文

=SUMPRODUCT(配列1, [配列2], [配列3], ...)

カッコの中に、計算したい配列やセル範囲を指定します。

引数の説明

引数必須/任意説明
配列1必須計算の対象となる最初の配列またはセル範囲
配列2, …任意掛け合わせたい追加の配列やセル範囲（最大255個まで指定可能）

引数が1つだけの場合は、単純にその範囲の合計を返します（SUM関数と同じ動作）。引数を2つ以上指定すると、対応する位置の要素同士を掛け算してから合計します。

ここで大事なポイントがひとつ。すべての配列は同じサイズ（行数・列数）にする必要があります。サイズが違うと #VALUE! エラーになるので注意してください。

SUMPRODUCT関数の基本的な使い方

実際にSUMPRODUCT関数を使ってみましょう。よくある「単価 x 数量」の合計を求める例で説明します。

次のような商品リストがあるとします。

A列（商品名）B列（単価）C列（数量）
1行目商品名単価数量
2行目ノート20050
3行目ペン15080
4行目消しゴム10030
5行目ファイル30020

全商品の売上合計を出すには、次の数式を入力します。

=SUMPRODUCT(B2:B5, C2:C5)

この数式は内部で次の計算を行っています。

(200×50) + (150×80) + (100×30) + (300×20)
= 10000 + 12000 + 3000 + 6000
= 31000

結果は 31,000 です。

もしSUMPRODUCT関数を使わないと、D列に「=B2*C2」と作業列を作り、さらに「=SUM(D2:D5)」と2ステップで計算する必要があります。SUMPRODUCT関数なら作業列なしで、1つの数式ですっきり書けるのがポイントです。

SUMPRODUCT関数の応用例｜条件付き集計

SUMPRODUCT関数の真価が発揮されるのは、条件付き集計の場面です。「特定の条件に合うデータだけを掛け算して合計したい」ときに大活躍します。

1つの条件で集計する

先ほどの商品リストにカテゴリ列を追加したデータで考えてみましょう。

A列（商品名）B列（カテゴリ）C列（単価）D列（数量）
1行目商品名カテゴリ単価数量
2行目ノート文房具20050
3行目ペン文房具15080
4行目マウスPC周辺機器200010
5行目ファイル文房具30020

「文房具」だけの売上合計を出したい場合、次のように書きます。

=SUMPRODUCT((B2:B5="文房具")*C2:C5*D2:D5)

ちょっと不思議な書き方に見えますよね。ポイントは (B2:B5="文房具") の部分です。

この部分は条件判定で、各セルが「文房具」と一致するかどうかを TRUE(1) / FALSE(0) で返します。結果を掛け算に含めることで、条件に合わない行は自動的に0になり、集計から除外される仕組みです。

(TRUE×200×50) + (TRUE×150×80) + (FALSE×2000×10) + (TRUE×300×20)
= (1×200×50) + (1×150×80) + (0×2000×10) + (1×300×20)
= 10000 + 12000 + 0 + 6000
= 28000

結果は 28,000 です。

複数条件で集計する

条件を2つ以上組み合わせることもできます。たとえば「文房具」で、かつ「単価が200以上」の売上合計を出す場合は次のように書きます。

=SUMPRODUCT((B2:B5="文房具")*(C2:C5>=200)*C2:C5*D2:D5)

条件部分をカッコで囲んで * でつなぐだけ。条件を増やしたいときも同じパターンで追加できます。

この柔軟さが、IF関数を何重にもネストするより圧倒的にシンプルです。

NOTE

条件付き集計はSUMIFS関数でも可能ですが、SUMPRODUCT関数はOR条件（「文房具」または「PC周辺機器」）や、計算式を条件に使う場合に特に便利です。

SUMPRODUCT関数でよくあるエラーと対処法

SUMPRODUCT関数を使っていて遭遇しやすいエラーと、その対処法を紹介します。

#VALUE! エラー

原因: 指定した配列のサイズ（行数・列数）が一致していない

=SUMPRODUCT(A1:A5, B1:B3)  ← 5行と3行で不一致

対処法: すべての配列の範囲を同じサイズに揃えましょう。

=SUMPRODUCT(A1:A5, B1:B5)  ← 両方5行に揃える

範囲を選択するときに、行数がずれていないか必ず確認してください。特にデータを追加したあとは要注意です。

数値が入っているのに結果が0になる

原因: セルの値が「数値に見える文字列」になっている可能性があります。

CSVファイルからデータを取り込んだ場合によく起きます。セルの左上に緑の三角マークが表示されていたら、文字列として格納されているサインです。

対処法: 対象のセルを選択して「数値に変換」を実行するか、VALUE関数 で明示的に数値に変換してから使いましょう。

=SUMPRODUCT(VALUE(A1:A5)*B1:B5)

条件式で結果がおかしい

原因: 条件部分のカッコが抜けている

=SUMPRODUCT(B2:B5="文房具"*C2:C5*D2:D5)  ← カッコ忘れ

対処法: 条件部分は必ずカッコで囲みましょう。

=SUMPRODUCT((B2:B5="文房具")*C2:C5*D2:D5)  ← 正しい

似た関数との違い・使い分け

SUMPRODUCT関数と似た機能を持つ関数との違いを整理しておきましょう。

SUM関数との違い

比較項目SUM関数SUMPRODUCT関数
基本機能数値の合計掛け算してから合計
配列の掛け算できないできる
条件付き集計できない（SUMIFSが必要）できる
書きやすさシンプルやや複雑

単純な合計ならSUM関数で十分です。「掛け算してから合計」や「条件付きの計算」が必要なときにSUMPRODUCT関数の出番です。

SUMIF / SUMIFS関数との違い

比較項目SUMIFS関数SUMPRODUCT関数
条件の指定方法引数で条件範囲と条件値を指定配列の比較式で条件を記述
OR条件直接はできない+ でOR条件が書ける
計算式を条件にするできないできる
わかりやすさ初心者向き中級者向き

シンプルな条件付き合計ならSUMIFS関数がおすすめです。一方で、OR条件を使いたい場合や、「単価 x 数量」のような計算結果を条件付きで集計したい場合は、SUMPRODUCT関数が最適です。

まとめ

この記事では、ExcelのSUMPRODUCT関数の使い方を基本から応用まで解説しました。

ポイントをおさらいしましょう。

  • SUMPRODUCT関数は「掛け算してから合計」を1つの数式で実行できる
  • 条件付き集計では (条件式)*集計範囲 の形で、条件に合うデータだけを集計できる
  • 複数条件は (条件1)(条件2) のように でつなげるだけ
  • 配列のサイズは必ず揃える（揃っていないと #VALUE! エラー）
  • 単純な合計にはSUM関数、シンプルな条件付き合計にはSUMIFS関数、柔軟な条件付き集計にはSUMPRODUCT関数と使い分けよう

最初はちょっと取っつきにくく感じるかもしれませんが、一度覚えてしまえば作業列を減らせてスプレッドシートがすっきりします。ぜひ実務で試してみてください。

スポンサーリンク
タイトルとURLをコピーしました