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

スポンサーリンク

「商品ごとの売上を出したいけど、単価と数量を1行ずつ掛けてから合計するのは面倒…」。Excelで集計作業をしていると、こんな場面に出くわしますよね。

作業列を増やすほどシートが散らかるし、ミスも起きやすくなります。

そんなときに頼りになるのが、ExcelのSUMPRODUCT関数です。「掛け算して合計」を1つの数式でこなせるうえ、条件付き集計やOR条件にも対応できる万能な関数ですよ。

この記事では、SUMPRODUCT関数の基本構文から条件付き集計、OR条件、加重平均まで、実例付きでわかりやすく解説します。

ExcelのSUMPRODUCT関数とは？

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

名前は「SUM（合計）」と「PRODUCT（積・掛け算）」の組み合わせ。文字どおり「掛け算の合計」を一発で求められます。

たとえば、商品リストの「単価」と「数量」があるとき、全商品の売上合計をワンステップで計算できます。通常なら作業列に「単価 x 数量」を入力してからSUM関数で合計しますが、SUMPRODUCT関数なら数式1つで済むわけです。

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

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

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

NOTE

SUMPRODUCT関数はExcel 2007以降のすべてのバージョンとMicrosoft 365で使えます。Googleスプレッドシートでも同じ構文で利用可能です。

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

基本構文

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

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

引数の説明

引数必須/任意説明
配列1必須計算の対象となる最初の配列またはセル範囲
配列2, 配列3, …任意掛け合わせたい追加の配列（最大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 x 50) + (150 x 80) + (100 x 30) + (300 x 20)
= 10,000 + 12,000 + 3,000 + 6,000
= 31,000

結果は 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周辺機器2,00010
5行目ファイル文房具30020

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

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

ポイントは (B2:B5="文房具") の部分です。この条件判定は、各セルが「文房具」かどうかを TRUE(1) / FALSE(0) で返します。結果を掛け算に含めることで、条件に合わない行が自動的に0になる仕組みです。

(TRUE x 200 x 50) + (TRUE x 150 x 80) + (FALSE x 2000 x 10) + (TRUE x 300 x 20)
= (1 x 200 x 50) + (1 x 150 x 80) + (0 x 2000 x 10) + (1 x 300 x 20)
= 10,000 + 12,000 + 0 + 6,000
= 28,000

結果は 28,000 です。

複数条件（AND）で集計する

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

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

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

計算過程を確認してみましょう。

文房具かつ単価200以上のデータ:
  ノート: 文房具=TRUE, 200>=200=TRUE → 1 x 1 x 200 x 50 = 10,000
  ペン:   文房具=TRUE, 150>=200=FALSE → 1 x 0 x 150 x 80 = 0
  マウス: PC周辺機器=FALSE             → 0 x ... = 0
  ファイル: 文房具=TRUE, 300>=200=TRUE → 1 x 1 x 300 x 20 = 6,000

合計 = 10,000 + 0 + 0 + 6,000 = 16,000

結果は 16,000 です。

OR条件で集計する

「文房具」または「PC周辺機器」のデータを集計したい場合、+ を使ってOR条件を作れます。

=SUMPRODUCT(((B2:B5="文房具")+(B2:B5="PC周辺機器"))*C2:C5*D2:D5)

* がAND条件、+ がOR条件と覚えておくとわかりやすいですよ。

ただし、OR条件のときはひとつ注意点があります。両方の条件に当てはまるデータがある場合、合計値が2倍になってしまいます。これを防ぐには、条件の結果を >0 で判定する方法がおすすめです。

=SUMPRODUCT(((B2:B5="文房具")+(B2:B5="PC周辺機器")>0)*C2:C5*D2:D5)

>0 を加えることで、条件の合計が1以上なら TRUE(1) に統一されます。重複カウントの心配がなくなりますよ。

NOTE

OR条件はSUMIFS関数では直接書けません。OR条件が必要なときこそ、SUMPRODUCT関数の出番です。

SUMPRODUCT関数の実務テクニック

条件付き集計以外にも、実務で使えるテクニックがあります。代表的な2つを紹介しますね。

加重平均を求める

「科目ごとに配点（重み）が違うテストの平均点を出したい」。こんなときは加重平均が必要です。

 A列（科目）B列（点数）C列（配点）
1行目科目点数配点
2行目数学803
3行目英語902
4行目国語701

加重平均の計算式はこうなります。

=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)

計算の流れを確認しましょう。

分子: (80 x 3) + (90 x 2) + (70 x 1) = 240 + 180 + 70 = 490
分母: 3 + 2 + 1 = 6
加重平均: 490 / 6 ≒ 81.7

AVERAGE関数の単純平均は (80+90+70)/3 = 80 ですが、配点を考慮した加重平均は 約81.7 になります。配点の大きい数学の比重が高くなるわけですね。

COUNTIFの代わりに条件付きカウントをする

SUMPRODUCT関数で条件に合うデータの件数を数えることもできます。

=SUMPRODUCT((B2:B5="文房具")*1)

条件式 (B2:B5="文房具") が返す TRUE/FALSE に *1 を掛けると 1/0 の数値になります。その合計がそのまま件数です。この例では 3 が返ります。

単純なカウントならCOUNTIF関数で十分です。ただし、「文房具で単価200以上」のように複数条件をANDやORで組み合わせたいときは、SUMPRODUCT関数のほうが柔軟に書けますよ。

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

この数式は「文房具かつ単価200以上」のデータを数えます。結果は 2 （ノートとファイル）です。

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

SUMPRODUCT関数を使っていると遭遇しやすいエラーを3つ紹介します。

#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)  ← 正しい

カッコを忘れると、"文房具"*C2:C5 が先に評価されて #VALUE! エラーになることもあります。条件式には必ずカッコを付ける、と覚えておくと安心ですよ。

SUMIF・SUMIFS関数との違い・使い分け

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

比較項目SUMIF関数SUMIFS関数SUMPRODUCT関数
条件の数1つ複数（AND）複数（AND + OR）
掛け算+合計できないできないできる
OR条件できない直接はできない+ で簡単に書ける
計算式を条件に使うできないできないできる
書きやすさシンプルわかりやすいやや複雑
処理速度速い速いやや遅い（大量データ時）

使い分けの目安は次のとおりです。

  • 条件が1つだけ → SUMIF関数がシンプルでおすすめ
  • 複数条件（AND） → SUMIFS関数が引数の形で条件を指定できて直感的
  • OR条件が必要 → SUMPRODUCT関数の出番
  • 「掛けてから合計」が必要 → SUMPRODUCT関数一択
  • 計算式を条件にしたい → SUMPRODUCT関数が最適

より詳しい比較は「SUMIF・SUMIFS・SUMPRODUCTの使い分け｜条件付き集計を完全攻略」で解説しています。

まとめ

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

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

  • SUMPRODUCT関数は「掛け算してから合計」を1つの数式で実行できる
  • 条件付き集計は (条件式)*集計範囲 の形で書く
  • 複数条件（AND）は (条件1)(条件2) のように でつなぐ
  • OR条件は (条件1)+(条件2) のように + でつなぐ
  • 加重平均は SUMPRODUCT(値, 重み) / SUM(重み) で求められる
  • 配列のサイズは必ず揃える（#VALUE! エラーの原因になる）
  • 単純な条件合計にはSUMIF・SUMIFS、柔軟な条件合計にはSUMPRODUCTと使い分けよう

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

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