【Excel】SUMPRODUCT関数で複数条件の集計をする方法

日々の業務でExcelを使っていて、複数の条件に合致するデータを集計したい場面はありませんか。

SUMPRODUCT関数は、そのような複雑な条件に基づいて合計値を算出する際に大変役立つ機能です。

この記事では、SUMPRODUCT関数を使って複数の条件を満たすデータを効率的に集計する手順を詳しく解説します。

SUMPRODUCT関数で複数条件集計を行う仕組み

SUMPRODUCT関数は、指定した複数の配列の対応する要素を乗算し、その積の合計を返す関数です。

この関数の特徴は、論理式の結果を数値として利用できる点にあります。

条件式「範囲=条件」は、条件に合致すればTRUE、合致しなければFALSEという論理値を返します。

これらの論理値に算術演算子(*1や–)を適用すると、TRUEは数値の1に、FALSEは数値の0に変換されます。

これにより、条件を満たす項目だけが1となり、集計対象として識別できるようになります。

複数の条件を乗算すると、全ての条件が1(TRUE)の場合のみ結果が1となり、AND条件として機能します。

条件式の間に加算(+)を使うと、いずれかの条件が1(TRUE)であれば結果が1以上となり、OR条件として利用できます。

SUMPRODUCT関数による複数条件集計の基本手順

ここでは、SUMPRODUCT関数を使って複数条件での合計値を算出する具体的な手順を解説します。

例として、商品名、地域、売上金額のデータから、「特定の商品」と「特定の地域」の組み合わせの売上合計を求めます。

1. データ準備
   Excelシートに集計したいデータを用意します。例えば、A列に商品名、B列に地域、C列に売上金額が入力されているとします。
2. 条件の指定
   集計したい商品名と地域を、それぞれ別のセルに入力しておくと便利です。例えば、商品名をF1セル、地域をG1セルに入力します。
3. AND条件の数式入力
   結果を表示したいセルに、以下の数式を入力します。
   =SUMPRODUCT((A2:A10=F1)*(B2:B10=G1)*(C2:C10))
   この数式では、(A2:A10=F1)でA列の商品名がF1セルの商品名と一致するかどうかを判定します。(B2:B10=G1)でB列の地域がG1セルの地域と一致するかどうかを判定します。両方の条件がTRUEの場合、その行のC列の値を合計します。
4. OR条件の数式入力
   いずれかの条件が当てはまれば集計したい場合は、条件式をプラス記号でつなぎます。例えば、「特定の商品A」または「特定の商品B」の売上合計を求めるには、以下の数式を入力します。
   =SUMPRODUCT(((A2:A10="商品A")+(A2:A10="商品B"))*(C2:C10))
   この数式では、(A2:A10="商品A")+(A2:A10="商品B")がTRUEになる行のC列の値を合計します。ただし、OR条件の場合、両方の条件を満たすと結果が2以上になるため、通常は--(二重マイナス)や*1で数値に変換してから合計します。二重マイナスを使用する例は以下です。
   =SUMPRODUCT(--((A2:A10="商品A")+(A2:A10="商品B")),C2:C10)
   または、条件のいずれかがTRUEであれば1に変換し、合計対象とすることもできます。この場合は、条件式の合計が0より大きいかどうかで判断します。
   =SUMPRODUCT((((A2:A10="商品A")+(A2:A10="商品B"))>0)*(C2:C10))
5. 数式確定と結果確認
   Enterキーを押して数式を確定し、計算結果を確認します。

SUMPRODUCT関数利用時の注意点とよくあるエラー

SUMPRODUCT関数は強力な機能ですが、正しく使うためにはいくつかの注意点があります。

配列サイズの不一致エラー(#VALUE!)

原因: SUMPRODUCT関数に渡す配列の範囲が異なる行数または列数になっていると、#VALUE!エラーが発生します。たとえば、A2:A10とC2:C12のように範囲がずれている場合です。

対処法: 数式内の全ての配列が同じ行数および列数になるように、範囲を修正してください。全ての範囲が同じ形状であることが必要です。

文字列や空白セルの扱い

原因: 数値として集計したい範囲に文字列や空白セルが含まれていると、計算結果がおかしくなったりエラーになったりする場合があります。特に、条件式の一部で範囲に文字列がある場合、その部分は無視されるか、エラーの原因になることがあります。

対処法: 集計対象の数値範囲は、数値のみで構成されていることを確認してください。文字列や空白セルを無視して計算したい場合は、エラー処理関数(IFERRORなど)や、ISNUMBER関数などで数値のみを対象とする条件を追加する検討もできます。

計算パフォーマンスへの影響

原因: SUMPRODUCT関数は、内部で多くの配列処理を行います。非常に大きなデータ範囲(数万行以上など)に対して複雑な条件で適用すると、計算に時間がかかり、Excelの動作が遅くなることがあります。

対処法: 大規模なデータセットでの集計が必要な場合は、可能であればSUMIFS関数やCOUNTIFS関数など、より特化した関数を使用することを検討してください。これらの関数は、SUMPRODUCT関数よりも効率的に処理されることが多いです。また、データの範囲を必要最小限に限定することも有効です。ExcelのバージョンがMicrosoft 365やExcel 2019以降であれば、特にSUMIFS関数が推奨されます。

OR条件での重複加算

原因: OR条件で複数の条件を足し算でつなぎ、そのまま乗算する場合、複数の条件に合致するデータが重複してカウントされる可能性があります。例:商品Aかつ地域X、商品Bかつ地域Y、の場合で「商品Aまたは商品B」の合計を取る際、両方の条件を満たす項目があればその項目が複数回加算されてしまいます。

対処法: OR条件を使用する際は、--(二重マイナス)や*1で論理値を0または1に変換し、さらに条件の合計が0より大きいかどうかを判定する((条件1)+(条件2))>0 のような形で、真偽値を0または1に正規化してから集計対象と乗算することで重複加算を避けます。あるいは、Power Queryなど別の集計ツールも検討できます。

SUMPRODUCT関数とSUMIFS関数・COUNTIFS関数の機能比較

項目	SUMPRODUCT関数	SUMIFS関数 / COUNTIFS関数
集計できる項目	合計、平均、個数、複雑な論理式に基づく集計	合計 (SUMIFS)、個数 (COUNTIFS)
条件の数	非常に多くの条件を設定可能	最大127個の条件を設定可能
条件の種類	AND条件、OR条件、NOT条件など柔軟な設定が可能	基本的なAND条件のみ(OR条件は数式の工夫が必要)
計算パフォーマンス	大規模データでは遅くなる可能性あり	SUMPRODUCT関数よりも高速に処理されることが多い
対応Excelバージョン	ほぼ全てのExcelバージョンで利用可能	Excel 2007以降で利用可能 (Excel 2019, 2021, Microsoft 365で推奨)
ワイルドカード	直接利用は難しいが、FIND/SEARCH関数と組み合わせ可能	「*」や「?」を使ったワイルドカード条件を直接利用可能

SUMPRODUCT関数と他の集計関数の使い分け

SUMPRODUCT関数は非常に柔軟性が高く、複雑な集計ニーズに対応できる強力な関数です。

特に、OR条件やNOT条件を含む複雑な条件式で合計や個数を集計したい場合に真価を発揮します。

一方、SUMIFS関数やCOUNTIFS関数は、主にAND条件でデータを集計する際に効率的です。

Excel 2007以降のバージョンを使用している場合は、まずSUMIFS/COUNTIFS関数の利用を検討し、それらで対応できない複雑な条件の場合にSUMPRODUCT関数を使うとよいでしょう。

パフォーマンスの面からも、シンプルなAND条件であればSUMIFS/COUNTIFSが優れています。

Excel2019・2021でのSUMPRODUCT関数の利用

SUMPRODUCT関数は、古いExcelバージョンから利用できる汎用性の高い関数です。

Excel 2019やExcel 2021でも、Microsoft 365版と同様の操作と機能で利用できます。

ただし、Microsoft 365に搭載されているFILTER関数やUNIQUE関数などの動的配列関数は、Excel 2019やExcel 2021にはありません。

そのため、これらのバージョンでは、SUMPRODUCT関数のような従来の配列を扱う関数がより重要になります。

しかし、前述の通り大規模なデータ集計では処理速度が問題になる場合があります。

状況に応じて、SUMIFS関数など他の集計関数との使い分けを検討してください。

SUMPRODUCT関数は、複数の条件に合致するデータを集計したいビジネスシーンで非常に有効な関数です。

この記事で解説した手順と注意点を参考に、複雑な条件集計をスムーズに行えるようになりました。

配列サイズの不一致エラーやパフォーマンスの問題に留意しつつ、SUMIFS関数などと使い分けながら効率的なデータ分析を進めてみてください。

様々な条件を組み合わせて、より詳細な分析結果を得るためにSUMPRODUCT関数を活用しましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】