Excelで「SUMIF関数では条件が足りない」「COUNTIFSとSUMIFSを何度も組み合わせているが数式が複雑になる」という壁にぶつかったとき、解決策として浮上するのがSUMPRODUCT関数です。
SUMPRODUCT関数は複数の配列を要素ごとに掛け合わせて合計する関数です。この仕組みを応用することで、複数条件のカウント・合計・重み付け平均・ユニーク件数の集計まで1つの関数で処理できます。MOS Excel試験でもSUMPRODUCTを使った配列演算は出題対象で、仕組みを理解していないとひっかけ問題でミスが増えます。本記事では基本構文から実務で役立つ応用パターン、COUNTIFSとの使い分け、MOS試験頻出ポイントまで体系的に解説します。
「SUMIF・COUNTIFだけでは条件が複雑になりすぎる」「重み付き平均を1セルで出したい」「MOS試験でSUMPRODUCT問題を落とさないようにしたい」という方はぜひ最後までご覧ください。
SUMPRODUCT関数の基本構文と配列演算の仕組み
SUMPRODUCT関数の書式は次のとおりです。
=SUMPRODUCT(配列1, [配列2], [配列3], ...)引数に指定した複数の配列（セル範囲）を要素ごとに掛け合わせ、その積の合計を返します。たとえば単価の列と個数の列を引数に渡すと、行ごとに「単価×個数」を計算してすべて足した売上合計が1式で求まります。
| セル | 単価（A列） | 個数（B列） | 行ごとの積 |
|---|---|---|---|
| 行1 | 500 | 3 | 1,500 |
| 行2 | 800 | 5 | 4,000 |
| 行3 | 1,200 | 2 | 2,400 |
| 合計 | — | — | 7,900 |
=SUMPRODUCT(A1:A3, B1:B3)と入力すると、1,500＋4,000＋2,400＝7,900が返ります。SUMIFでは配列同士の積演算ができないため、SUMPRODUCT関数が活躍します。
引数が1つのときの動作
引数を1つだけ指定すると、その配列の要素をすべて合計します。=SUMPRODUCT(A1:A3)はSUM(A1:A3)と同じ結果です。ただし後述する条件式と組み合わせる場合に1引数形式を使うことが多いため、動作を確認しておきましょう。
条件付きカウントへの応用──COUNTIF代替パターン
SUMPRODUCT関数の強力な応用が条件式（論理値）を数値に変換して集計する手法です。条件式はTRUEを1、FALSEを0として扱われます。
たとえばA1:A10に部署名が入っていて「営業」の件数を数えるには次のように書きます。
=SUMPRODUCT((A1:A10="営業")*1)A1:A10="営業"は各セルがTRUEかFALSEの配列を返します。それに*1を掛けることで1と0の配列に変換され、合計が「営業」の件数になります。なお--（ダブルマイナス）も同じ変換に使われます。
=SUMPRODUCT(--(A1:A10="営業"))どちらも結果は同じです。好みとチームの慣習に合わせて選んでください。
複数条件の集計──COUNTIFSとSUMIFSとの使い分け
複数条件の集計にはCOUNTIFSやSUMIFSが使えますが、SUMPRODUCT関数でも同等の処理が可能です。
複数条件カウントの比較
| 方法 | 数式例（部署=営業 かつ 売上>=10000） | 特徴 |
|---|---|---|
| COUNTIFS | =COUNTIFS(A:A,”営業”,B:B,”>=”&10000) | シンプル・可読性が高い |
| SUMPRODUCT | =SUMPRODUCT((A2:A100=”営業”)*(B2:B100>=10000)) | 範囲指定が柔軟・配列演算と組み合わせやすい |
条件が2～3個で済む場合はCOUNTIFSの方が読みやすいです。一方SUMPRODUCTは条件に計算式や複数範囲を組み込みたい場合、動的に条件を変えたい場合に優れています。特にセル参照を条件に使う場合の柔軟性が高い点が実務での強みです。
複数条件合計の比較
合計の場合はSUMIFSとの比較になります。C列に売上額が入っているとして「部署=営業かつ売上>=10000の合計」を求める例です。
=SUMIFS(C2:C100, A2:A100, "営業", B2:B100, ">="&10000)
=SUMPRODUCT((A2:A100="営業")*(B2:B100>=10000)*C2:C100)SUMPRODUCT版は条件を*でチェーンしてから集計列を最後に掛けるパターンです。条件が4個以上になるとSUMIFSも引数が増えて読みにくくなるため、SUMPRODUCTに統一するチームもあります。
重み付け平均の計算
SUMPRODUCT関数が最も「関数の本来用途」を発揮するのが重み付け平均です。単純なAVERAGE関数では重みを考慮できませんが、SUMPRODUCT÷SUMで実現できます。
例：科目ごとに単位数（重み）と成績点が異なる場合のGPA計算。
| 科目 | 単位数（B列） | 成績点（C列） |
|---|---|---|
| 統計学 | 4 | 85 |
| 英語 | 2 | 72 |
| プログラミング | 3 | 91 |
=SUMPRODUCT(B2:B4, C2:C4) / SUM(B2:B4)計算結果：(4×85 + 2×72 + 3×91)÷(4+2+3) ＝ (340+144+273)÷9 ＝ 757÷9 ≒ 84.1。単純平均の(85+72+91)÷3≒82.7と比べ、単位数の多い科目の成績が正しく反映されます。在庫管理では品目ごとに仕入数量と仕入単価の加重平均を出すのに同じパターンが使えます。
ユニーク件数のカウント──COUNTIF組み合わせパターン
Excel 2019以前や、UNIQUE関数が使えない環境でリストに含まれる重複を除いた種類数を数えるにはSUMPRODUCTとCOUNTIFを組み合わせます。
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))仕組み：COUNTIF(A2:A100, A2:A100)はリスト内の各要素が何回出現するかを配列で返します。たとえば「東京」が3回あれば3つの「3」が並びます。それぞれの逆数（1÷3）を合計すると、3つで1になり、ちょうど1種類分としてカウントされます。
ただしA列に空白セルがあるとCOUNTIFが0を返して「#DIV/0!エラー」が発生します。空白を除外するには次のようにします。
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&""))なおExcel 2021・Microsoft 365環境では=COUNTA(UNIQUE(A2:A100))の方がシンプルです。バージョンを確認して使い分けてください。
SUMPRODUCT関数でよく出るエラーと対処法
| エラー | 原因 | 対処法 |
|---|---|---|
| #VALUE! | 配列の行数・列数が一致していない | すべての引数の範囲サイズを揃える（例: A2:A100とB2:B100） |
| #DIV/0! | ユニークカウント式で空白セルに1/0が発生 | 空白除外条件 (A2:A100<>””) を掛ける |
| 結果が0になる | 文字列型数値が混在している | VALUE関数で数値変換するか、–演算子で強制変換する |
| 計算が遅い | 対象範囲が列全体（A:A）になっている | 使用範囲（A2:A10000など）に絞って指定する |
配列サイズの不一致が最多エラーです。SUMPRODUCTはすべての配列の行数と列数が同一でなければ#VALUE!を返します。数式を書いた後に範囲が揃っているか確認する習慣をつけましょう。
MOS Excel試験でのSUMPRODUCT出題ポイント
MOS Excel 365＆2019のエキスパートレベルでは、SUMPRODUCT関数を使った集計問題が出題されます。試験で問われる主なポイントは次のとおりです。
- 基本的な積和計算：複数列の掛け算合計を求める（単価×個数のような例題）
- 条件付き集計：条件式と掛け合わせて特定行の合計・件数を求める
- 引数の配列サイズ：引数に渡す範囲のサイズが揃っていることを確認する問題
- Ctrl+Shift+Enterが不要：SUM+IF配列数式との違いを理解する
MOS試験ではSUMIF・COUNTIFが解けるかどうかと同時に、「なぜSUMPRODUCTが必要な場面があるのか」を理解しているかが問われます。「配列を要素ごとに演算して合計」という本質的な動作を押さえておけば、試験問題が変わっても対応できます。
SUM＋IF配列数式との違い
かつてExcel 2007以前では条件付き集計をするためにSUM関数とIF関数を組み合わせた配列数式（Ctrl+Shift+Enterで確定）を使う場面がありました。SUMPRODUCT関数は通常のEnterで確定できるため入力が楽で、間違いが起きにくい利点があります。
| 方法 | 入力確定 | 可読性 | Excel版 |
|---|---|---|---|
| SUM+IF配列数式 | Ctrl+Shift+Enter | 中 | 全バージョン |
| SUMPRODUCT | Enter | 高 | 全バージョン |
| SUMIFS / COUNTIFS | Enter | 最高 | 2007以降 |
Excel 2007以降であれば単純な複数条件集計はSUMIFS・COUNTIFSを優先し、複合的な演算や重み付け計算にSUMPRODUCTを使うのが現代的なスタイルです。
実務で使えるSUMPRODUCT活用シナリオ集
SUMPRODUCT関数が実際の業務で活躍するシナリオをまとめます。
| シナリオ | 数式パターン |
|---|---|
| 在庫残高が0未満の品目数を数える | =SUMPRODUCT((C2:C100<0)*1) |
| 特定月・特定担当者の売上合計 | =SUMPRODUCT((MONTH(A2:A100)=6)*(B2:B100=”田中”)*C2:C100) |
| テスト結果が合格ライン以上の件数 | =SUMPRODUCT((B2:B50>=60)*1) |
| 複数商品の加重平均単価 | =SUMPRODUCT(B2:B30,C2:C30)/SUM(B2:B30) |
| 名簿から重複なしの出身地の種類数 | =SUMPRODUCT(1/COUNTIF(D2:D50,D2:D50)) |
日付の月判定で使っているMONTH関数のように、SUMPRODUCT内では関数の返す配列も条件として使えます。これがSUMIFSにはできないSUMPRODUCTの強みのひとつです。
まとめ：SUMPRODUCT関数で「1式で解決」できる場面を増やす
SUMPRODUCT関数のポイントをまとめます。
- 基本動作：複数配列を要素ごとに掛け合わせ合計する関数
- 条件付き集計：条件式（TRUE/FALSE）を*1や–で数値化して集計できる
- 重み付き平均：SUMPRODUCT(重み列, 値列)/SUM(重み列)のパターンで算出
- ユニークカウント：1/COUNTIFを組み合わせて重複除外件数を算出
- Ctrl+Shift+Enter不要：SUM+IF配列数式より入力しやすく間違いが少ない
- MOS試験：エキスパートレベルで出題。「配列の積和」という本質を押さえる
「複数条件の集計はSUMIFSに任せ、SUMPRODUCT関数は配列演算・重み付け・ユニークカウントに使う」という分業が現代Excelの標準スタイルです。まずは単価×個数パターンで動作を体感し、次第に条件式との組み合わせへ発展させていくと習得しやすいでしょう。MOS試験対策としても、SUMPRODUCT関数の基本動作と条件付き集計パターンを手を動かして練習しておくことをおすすめします。
