SUMPRODUCT関数の使い方｜配列演算のしくみとOR条件の集計

SUMPRODUCT関数は、対応する行同士を掛け合わせて、その合計を返す関数です。単価×数量の売上合計を、作業列なしで1つの式にできます。さらに条件式を掛け合わせると、条件つきの集計にもなるのが特徴で、OR条件や列同士の掛け算など、SUMIFS関数では書けない集計を引き受けます。

この記事でわかること

SUMPRODUCT関数の構文と引数

構文

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

「配列」という言葉が付いていますが、実際に指定するのは普通のセル範囲です。SUMPRODUCTは、その範囲の1行目同士・2行目同士……を順に掛け、最後にすべてを足します。

SUMPRODUCT関数はExcel 2016以前から使える関数です。Ctrl＋Shift＋Enterで確定する必要がなく、どのバージョンでも同じ書き方が通ります。

基本の使い方：単価×数量の合計

B列に単価、C列に数量が並ぶ表で、売上合計を出す式です。

=SUMPRODUCT($B$2:$B$100, $C$2:$C$100)

行ごとの金額を計算する作業列が要りません。作業列を作って =B2*C2 を100行入れ、最後にSUMで合計する手順と結果は同じですが、列が1本減り、行を追加したときの数式のコピー漏れも起きません。

重み付き平均を出す

同じ考え方で、数量で重みづけした平均単価が求められます。

=SUMPRODUCT($B$2:$B$100, $C$2:$C$100) / SUM($C$2:$C$100)

売上合計を数量合計で割る形です。単価列をそのままAVERAGEで平均すると、数量の多い商品も1件の商品も同じ重みになってしまいます。

範囲の大きさは必ずそろえる

$B$2:$B$100 と $C$2:$C$101 のように行数が食い違うと #VALUE! になります。行を追加するときは、すべての範囲を同時に広げる必要があります。

配列演算のしくみ

SUMPRODUCTの本領は、範囲の位置に条件式を書いたときに出ます。ここだけは理屈を押さえておくと、以降の応用がすべて同じ形に見えてきます。

条件式は行ごとに TRUE / FALSE を返す

$A$2:$A$100="東京" と書くと、Excelは範囲の1行ずつに対して判定を行い、TRUE と FALSE が縦に並んだ結果を作ります。

掛け算すると 1 と 0 になる

TRUE と FALSE は、計算に使われた瞬間に TRUE＝1・FALSE＝0 として扱われます。そこで金額の列を掛けると、条件を満たす行だけが金額のまま残り、満たさない行は0になります。

=SUMPRODUCT(($A$2:$A$100="東京") * $C$2:$C$100)

条件を増やすときは、掛け算を足していきます。掛け算はAND条件です。

=SUMPRODUCT(($A$2:$A$100="東京") * ($B$2:$B$100="ペン") * $C$2:$C$100)

件数を数えるなら *1

金額を掛ける代わりに 1 を掛けると、条件を満たす行数が返ります。

=SUMPRODUCT(($A$2:$A$100="東京") * 1)

-- を頭に付ける書き方も同じ意味です。マイナスを2回かけて TRUE/FALSE を 1/0 に変換しています。

=SUMPRODUCT(--($A$2:$A$100="東京"))

⚠️ * と , の使い分け

条件式を使うときは * が必要です。ただしそのぶん、掛ける対象の列に文字列が1つでも混ざるとエラーになるという副作用が付いてきます。

SUMIFSでは書けない3つの集計

条件つきの合計は、多くの場面でSUMIFS関数のほうが短く速く書けます。SUMPRODUCTを選ぶ理由は、SUMIFSでは表現できない形が3つあることです。

① 列同士を掛けてから合計する

SUMIFSが合計できるのは、すでに存在する1つの列だけです。「単価×数量を、東京の行だけ合計する」のように、掛け算した結果を条件つきで合計することはできません。

=SUMPRODUCT(($A$2:$A$100="東京") * $B$2:$B$100 * $C$2:$C$100)

作業列を作れば SUMIFS でも書けますが、列を1本増やさずに済むのがこちらの利点です。

② OR条件で集計する

SUMIFSの複数条件は、すべてAND（かつ）で結ばれます。「東京または大阪」を1つの式で書くには、掛け算ではなく足し算を使います。

=SUMPRODUCT((($A$2:$A$100="東京") + ($A$2:$A$100="大阪")) * $C$2:$C$100)

⚠️ 足し算のOR条件には注意点が1つあります。同じ列の等値条件なら1つの行が両方に当てはまることはありませんが、別の列を組み合わせたOR条件では、両方に当てはまる行が「2」になり、金額が二重に足されます。

そこで、足した結果を >0 で1/0に戻します。

=SUMPRODUCT(((($A$2:$A$100="東京") + ($B$2:$B$100="ペン")) > 0) * $C$2:$C$100)

OR条件を書くときは、>0 で挟む形を既定にしておくと事故が起きません。

③ 関数の結果を条件にする

条件の位置には関数も書けます。「曜日が月曜の行だけ」「文字列にキーワードを含む行だけ」といった、値そのものではなく計算した結果での絞り込みができます。

=SUMPRODUCT((WEEKDAY($A$2:$A$100,2)=1) * $C$2:$C$100)

=SUMPRODUCT(ISNUMBER(SEARCH("ペン",$B$2:$B$100)) * $C$2:$C$100)

SUMPRODUCTでは * や ? のワイルドカードが使えないため、部分一致は SEARCH関数と ISNUMBER関数の組み合わせで表現します。SEARCH関数は大文字と小文字を区別しません。

うまくいかないときの原因と直し方

⚠️ 列全体の指定は避ける

SUMIF関数などと違い、SUMPRODUCTは指定された範囲をすべて計算対象にします。A:A と書くと100万行以上を毎回計算するため、式の本数が増えると再計算が目に見えて遅くなります。

必要な行数で区切るか、テーブル機能で範囲を自動的に伸縮させる形にしてください。

結果が0のときの切り分け

条件つきの集計が0を返すときは、条件側と合計側を分けて確かめると原因が絞れます。

1. =SUMPRODUCT(($A$2:$A$100="東京")*1) — 0なら条件が1件も成立していない（表記ゆれ・全角半角）
2. =SUM($C$2:$C$100) — 0なら金額が文字列になっている

条件が成立していて金額も数値なら、掛け合わせた結果が0になることはありません。

似た関数との使い分け

判断の順番は決まっています。まずSUMIFSで書けるか試し、書けないときだけSUMPRODUCTに移る。SUMIFSのほうが読みやすく、大きな表でも軽く動きます。

Excel 2021・Microsoft 365 ならSUMでも書ける

動的配列に対応した環境では、SUMでも同じ形が通ります。

=SUM(($A$2:$A$100="東京") * $C$2:$C$100)

2019以前の環境では、この式を Ctrl＋Shift＋Enter で確定しないと正しく計算されません。SUMPRODUCTなら、どのバージョンでも通常のEnterで確定できます。バージョンが混在する職場で共有するブックでは、この点が選ぶ理由になります。

よくある質問

=SUMPRODUCT(($A$2:$A$100>=DATE(2026,4,1)) * ($A$2:$A$100<=DATE(2026,4,30)) * $C$2:$C$100)

=SUMPRODUCT(EXACT($A$2:$A$100,"ABC") * $C$2:$C$100)

まとめ