SUMPRODUCTとは？複雑な条件集計に強い理由
Excelでデータ集計をしていると、「部署が営業部で、商品カテゴリがAで、さらに売上が一定以上のものだけ合計したい」といった、少し複雑な条件集計が必要になる場面があります。そんなときに覚えておくと便利なのがSUMPRODUCT関数です。
SUMPRODUCTは、もともと「配列同士を掛け合わせ、その合計を返す」関数です。たとえば、単価と数量を掛けて売上金額を求め、それらをまとめて合計するような処理に使えます。関数名を分解すると、SUM＝合計、PRODUCT＝積という意味です。
一見すると単純な計算用の関数に見えますが、SUMPRODUCTの強みは、条件判定と組み合わせることで複数条件の集計に対応できる点にあります。たとえば、次のような集計が可能です。
- 特定の担当者だけの売上を合計する
- 複数の条件をすべて満たすデータだけを集計する
- 「AまたはB」のようなOR条件で集計する
- 日付範囲や数値条件を指定して合計する
通常、条件付きの合計といえばSUMIFやSUMIFSを思い浮かべる人も多いでしょう。もちろん、単純な条件集計であればSUMIFSの方が分かりやすく、実務でもよく使われます。しかし、SUMIFSでは対応しづらいケースもあります。たとえば、OR条件を含む集計や、条件ごとに計算を加えながら合計したい場合です。
SUMPRODUCTは、条件に一致する場合をTRUE、一致しない場合をFALSEとして判定し、それを数値の1と0に変換して計算します。つまり、「条件に合う行だけを1として残し、条件に合わない行は0にして除外する」という仕組みで集計しているのです。
この考え方を理解すると、SUMPRODUCTは単なる合計関数ではなく、データを条件でフィルタリングしながら集計できる関数だと分かります。大量の売上データ、勤怠データ、在庫データなどを扱うビジネスシーンでは、集計作業の時間短縮に大きく役立ちます。
特に20代のビジネスパーソンにとって、Excelで素早く正確にデータを集計するスキルは、資料作成や分析業務で差がつくポイントです。SUMPRODUCTを使いこなせるようになると、「手作業でフィルターをかけて合計する」といった面倒な作業を減らし、ミスの少ないスマートな集計ができるようになります。
基本形を理解しよう：SUMPRODUCTで「条件に合う数値」を合計する方法
SUMPRODUCTで条件集計を行うには、まず基本形を押さえることが大切です。難しそうに見えますが、考え方はシンプルで、「条件に合う行だけを残して、対象の数値を合計する」という流れです。
たとえば、次のような売上データがあるとします。
| 部署 | 担当者 | 売上 |
|---|---|---|
| 営業部 | 田中 | 100000 |
| 営業部 | 佐藤 | 150000 |
| 企画部 | 鈴木 | 120000 |
このデータで「営業部の売上だけを合計したい」場合、SUMPRODUCTでは次のように書きます。
=SUMPRODUCT((A2:A4="営業部")*(C2:C4))
この数式では、A2:A4="営業部" の部分で、部署が営業部かどうかを判定しています。営業部であればTRUE、そうでなければFALSEになります。そして、SUMPRODUCTの中で掛け算を行うことで、TRUEは1、FALSEは0として扱われます。
つまり、内部的には次のようなイメージです。
{1;1;0} * {100000;150000;120000}営業部の行は1が掛けられるため売上がそのまま残り、企画部の行は0が掛けられるため合計対象から除外されます。結果として、100000 + 150000 = 250000 が返されます。
このように、SUMPRODUCTでは条件判定の結果を1と0に変換し、集計したい数値範囲と掛け合わせることで、条件に一致するデータだけを合計できます。
基本の書き方は、次の形で覚えておくと分かりやすいです。
=SUMPRODUCT((条件範囲=条件値)*合計範囲)たとえば、担当者が「田中」の売上だけを合計したい場合は、次のようになります。
=SUMPRODUCT((B2:B4="田中")*(C2:C4))また、条件値をセル参照にしておくと、実務ではより使いやすくなります。たとえば、E2セルに「営業部」と入力しておき、次のように指定します。
=SUMPRODUCT((A2:A4=E2)*(C2:C4))こうしておけば、E2セルの値を「企画部」や「営業部」に変更するだけで、集計結果を簡単に切り替えられます。レポートや集計表を作るときに、毎回数式を書き換える必要がないため便利です。
注意点として、条件範囲と合計範囲の行数は必ずそろえる必要があります。たとえば、条件範囲がA2:A10なのに、合計範囲がC2:C9になっていると、正しく計算できなかったりエラーの原因になったりします。
まずはこの基本形を理解できれば、SUMPRODUCTの応用もぐっと分かりやすくなります。次の章では、複数の条件を組み合わせたAND条件・OR条件の集計方法を見ていきましょう。
AND条件・OR条件を使った複数条件集計の実践テクニック
SUMPRODUCTの基本形が分かったら、次に押さえたいのがAND条件とOR条件です。実務では「営業部かつ商品A」「田中さんまたは佐藤さん」のように、複数の条件を組み合わせて集計する場面がよくあります。
AND条件：すべての条件を満たすデータだけを合計する
AND条件は、複数の条件をすべて満たす場合だけ集計する考え方です。SUMPRODUCTでは、条件同士を*で掛け合わせて表現します。
たとえば、A列に部署、B列に商品カテゴリ、C列に売上があるとして、「営業部」かつ「商品A」の売上だけを合計したい場合は、次のように書きます。
=SUMPRODUCT((A2:A10="営業部")*(B2:B10="商品A")*(C2:C10))
この数式では、A2:A10="営業部"とB2:B10="商品A"の両方を判定しています。どちらもTRUEの行だけが1×1=1となり、どちらか一方でも条件に合わない行は0になります。
つまり、掛け算を使うことで「条件1も条件2も満たす」というAND条件を作っているわけです。
数値条件や日付条件も組み合わせられる
SUMPRODUCTでは、文字だけでなく数値や日付の条件も指定できます。たとえば、「営業部」かつ「売上が10万円以上」のデータ件数を集計したい場合は、次のようにします。
=SUMPRODUCT((A2:A10="営業部")*(C2:C10>=100000))売上金額そのものを合計したい場合は、最後に合計範囲を掛けます。
=SUMPRODUCT((A2:A10="営業部")*(C2:C10>=100000)*(C2:C10))日付条件も同じ考え方です。たとえば、D列に日付があり、「2024年4月1日以降」の営業部売上を合計するなら、次のように書けます。
=SUMPRODUCT((A2:A10="営業部")*(D2:D10>=DATE(2024,4,1))*(C2:C10))OR条件：いずれかの条件を満たすデータを集計する
一方、OR条件は複数の条件のうち、どれか1つでも満たせば集計する考え方です。SUMPRODUCTでは、条件同士を+で足して表現します。
たとえば、「担当者が田中または佐藤」の売上を合計したい場合は、次のように書きます。
=SUMPRODUCT(((B2:B10="田中")+(B2:B10="佐藤"))*(C2:C10))この場合、田中の行または佐藤の行は1になり、それ以外の行は0になります。その結果、2人分の売上だけを合計できます。
OR条件の重複カウントに注意
OR条件で注意したいのが、条件が重なるケースです。たとえば「営業部または売上10万円以上」のように指定すると、営業部であり、かつ売上10万円以上の行は、条件を2つ満たすため2として計算されてしまうことがあります。
重複を避けたい場合は、条件の合計が0より大きいかどうかを判定します。
=SUMPRODUCT((((A2:A10="営業部")+(C2:C10>=100000))>0)*(C2:C10))
>0を付けることで、「条件に1つ以上当てはまる行」を1として扱えるため、同じ行を二重に集計するミスを防げます。
まとめると、SUMPRODUCTではAND条件は掛け算、OR条件は足し算と覚えるのがコツです。このルールを理解しておくと、SUMIFSでは少し書きづらい複雑な条件集計も、1つの数式でスマートに処理できるようになります。
業務で使えるSUMPRODUCT活用例：売上・勤怠・在庫データを効率集計
SUMPRODUCTは、考え方さえ押さえれば、日々の業務データ集計にそのまま活用できます。ここでは、ビジネスシーンでよく扱う売上データ、勤怠データ、在庫データを例に、実務で使いやすい集計パターンを紹介します。
売上データ：期間と担当者を指定して売上を集計する
まずは売上管理でよくあるケースです。A列に日付、B列に担当者、C列に売上金額があるとします。このとき、「田中さんの2024年4月分の売上」を合計したい場合は、次のように書けます。
=SUMPRODUCT((B2:B100="田中")*(A2:A100>=DATE(2024,4,1))*(A2:A100<=DATE(2024,4,30))*(C2:C100))この数式では、担当者が田中さんで、日付が4月1日から4月30日までの行だけを抽出し、その売上金額を合計しています。月次レポートや営業成績の集計で使いやすい形です。
さらに、担当者名や開始日・終了日をセル参照にしておけば、集計条件を変更するたびに数式を書き換える必要がありません。
=SUMPRODUCT((B2:B100=E2)*(A2:A100>=F2)*(A2:A100<=G2)*(C2:C100))E2に担当者名、F2に開始日、G2に終了日を入力する設計にしておくと、集計表としてかなり実用的になります。
勤怠データ：条件に合う残業時間を合計する
勤怠管理でもSUMPRODUCTは便利です。たとえば、A列に部署、B列に社員名、C列に勤務日、D列に残業時間があるとします。「営業部の平日分の残業時間だけを合計したい」といった集計も可能です。
=SUMPRODUCT((A2:A100="営業部")*(WEEKDAY(C2:C100,2)<=5)*(D2:D100))
WEEKDAY(C2:C100,2)<=5は、日付が月曜から金曜かどうかを判定しています。これにより、土日を除いた営業部の残業時間だけを集計できます。人事や総務の勤怠チェック、部署別の工数確認などに役立ちます。
在庫データ：条件に合う商品の在庫金額を求める
在庫管理では、単純な数量だけでなく、在庫金額を確認したい場面があります。A列にカテゴリ、B列に商品名、C列に在庫数、D列に単価がある場合、「カテゴリがPC周辺機器の在庫金額」を求めるには、次のようにします。
=SUMPRODUCT((A2:A100="PC周辺機器")*(C2:C100)*(D2:D100))この数式では、対象カテゴリの行だけを残し、在庫数と単価を掛け合わせて合計しています。つまり、条件に合う商品の在庫評価額を一発で計算できるわけです。
SUMPRODUCTは、単に「条件に合う数値を足す」だけでなく、売上金額や在庫金額のように行ごとに計算してから合計する処理が得意です。手作業でフィルターをかけたり、補助列を作ったりする手間を減らせるため、集計作業のスピードアップにつながります。
エラーを防ぐコツと、SUMIFSとの使い分けポイント
SUMPRODUCTは便利な一方で、数式の作り方を少し間違えるとエラーになったり、意図しない結果になったりします。実務で安心して使うためには、よくあるミスと対策を押さえておきましょう。
範囲のサイズは必ずそろえる
もっとも多いミスが、条件範囲と集計範囲の行数がズレているケースです。たとえば、次のような数式はエラーの原因になります。
=SUMPRODUCT((A2:A100="営業部")*(C2:C99))条件範囲はA2:A100なのに、合計範囲がC2:C99になっています。SUMPRODUCTでは、掛け合わせる配列のサイズをそろえる必要があるため、次のように統一しましょう。
=SUMPRODUCT((A2:A100="営業部")*(C2:C100))文字列や空白が混ざっていないか確認する
合計範囲に文字列が混ざっていると、正しく計算できない場合があります。特に、CSVから取り込んだデータでは、数字に見えても実は文字列として保存されていることがあります。
「計算結果がおかしい」と感じたら、対象列の表示形式や空白、不要なスペースを確認しましょう。必要に応じて、VALUE関数で数値化したり、データのクリーニングをしてから集計すると安全です。
全列指定はできるだけ避ける
SUMPRODUCTでは、A:AやC:Cのような全列指定も使えますが、データ量が多いと処理が重くなりやすいです。PCの動作が遅くなったり、ファイルを開くたびに再計算に時間がかかったりする原因になります。
実務では、A2:A1000のように必要な範囲だけを指定するのがおすすめです。データが増える場合は、Excelのテーブル機能を使って範囲を管理すると、メンテナンスもしやすくなります。
SUMIFSとSUMPRODUCTはどう使い分ける？
条件付き集計では、SUMIFSもよく使われます。基本的には、シンプルな複数条件の合計ならSUMIFS、複雑な条件や行ごとの計算を含むならSUMPRODUCTと考えると分かりやすいです。
| 使いたい場面 | おすすめ関数 |
|---|---|
| 部署と担当者を指定して売上を合計する | SUMIFS |
| 「田中または佐藤」のようなOR条件で集計する | SUMPRODUCT |
| 在庫数×単価のように行ごとに計算して合計する | SUMPRODUCT |
| 数式を読みやすく保ちたい単純集計 | SUMIFS |
SUMIFSは数式が読みやすく、後から見返しても条件が分かりやすいのがメリットです。一方、SUMPRODUCTは自由度が高く、SUMIFSでは書きづらい条件にも対応できます。
つまり、普段の集計ではまずSUMIFSを検討し、「OR条件を入れたい」「掛け算してから合計したい」「条件判定を柔軟に組み合わせたい」と感じたらSUMPRODUCTを使うのが効率的です。
SUMPRODUCTを使いこなせると、Excelでできる集計の幅が一気に広がります。エラーを防ぐ基本を押さえつつ、SUMIFSとうまく使い分けて、日々の集計作業をよりスマートに進めていきましょう。
コメント