SUMPRODUCT関数で縦横複数条件の合計を求める方法！VBAによる一括設定も！

Excelで横の行と縦の列それぞれの条件にあった値を合計したいときはないでしょうか。

そんな時に便利なのはSUMPRODUCT関数です。SUM関数とIF関数の組み合わせでも可能ですが、SUMPRODUCT関数であれば1つで条件設定と合計を同時に計算してくれます。

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

ExcelでSUMPRODUCT関数を使用するイメージ

ExcelでSUMPRODUCT関数を使用するイメージについて説明をします。

SUMPRODUCT関数とは、複数のセル範囲（配列）を指定すると、範囲の値を掛け合わせて合計したり、条件式と範囲を指定するとその条件に合致した値の合計を返す関数です。
今回は縦横複数条件を指定するということで、後者の方法を利用していきます。

例えばこんな表があり、

行の「産地」「商品名」と列の「売上月」を指定し、その条件にあった範囲の値の合計値を返します。

また条件を完全一致から部分一致にしたり、

等号・不等号による～以上や否定の条件指定もできます。

さらにVBAでSUMPRODUCT関数を一括設定することもできます。

説明だけですと理解しにくいので、

実際に使用しながら理解していきましょう！

SUMPRODUCT関数を使用する表を用意する

SUMPRODUCT関数を使用する表を用意しましょう。

縦横複数条件の合計を求めるサンプル表は以下のようA列を産地、B列を商品名、C列、D列を売上月の合計にしました。

SUMPRODUCT関数の基本的な使い方のためのサンプル表はA列を産地、B列を商品名、C列を単価、D列を販売個数にしました。

SUMPRODUCT関数の書式/機能/引数について

SUMPRODUCT関数の詳細について説明をします。
SUMPRODUCT関数の書式と機能については以下の通りです。

SUMPRODUCT関数で2つの範囲の値を掛け合わせて合計する

2つの配列を掛け合わせ合計する

まずはSUMPRODUCT関数の基本となる2つの配列を掛け合わせその値の合計を求めてみましょう。
表はこちらを使います。

まずは、B2へ以下のように入力します。

C列の単価の範囲とD列の販売個数を掛け合わせ合計します。

はい、2つの範囲を掛け合わせた値の合計がとれましたね。

条件にあった2つの配列を掛け合わせ合計する

次は条件を追加し、条件にあった2つの配列を掛け合わせ合計してみましょう。

A列産地の値「千葉」に合致した値のみにするようにします。

数式をB10セルへ入力します。

「千葉」のみの合計がとれましたね。

タカヒロ

条件の文字列に不等号を加えると、その日付以降などの指定が可能です。詳細は以下をご参照ください。

SUMPRODUCT関数で縦横複数条件の合計する

SUMPRODUCT関数で縦横複数の条件に一致した値の合計を取得してみましょう。

サンプルの表は以下のものに変更します。

縦横2つの条件（産地・月別売上）の合計を求める

縦（列）、横（行）2つの条件（産地・月別売上）の合計を求めてみましょう。

産地と月別売上の合計を求めていきます。

A12セルに「千葉」、B12セルに「11月売上」を入力します。

数式を以下のように入力します。

はい、複数条件の合計が取得できましたね。

他の条件も設定してみましょう。

産地、売上月を入力し、数式をドラッグしていきます。

こちらも複数条件の合計が取得できましたね。

関数の引数について説明をします。

第一引数の

ですが、なぜ条件式どうしを掛け合わせているかわからないと思うかもしれません。

こちらは掛け算をしているというわけではなく、

A2～A9の件数、C1～D1の件数分、条件判定を繰り返すという意味となります。

また条件に一致するとTrueを返します。以下のように条件式だけにするとTrueの合計値が出力されます。

SUMPRODUCT関数の処理は、条件判定でTrueとなったセルの値のみ取得し合計していくという形となります。

タカヒロ

正直申し上げますと、SUMPRODUCT関数は少々複雑です…
まずはこういうものだと思ってもらえばとおもいます。

縦1つ横2つの条件（産地・商品名・月別売上）の合計を求める

縦（列）1つ、横（行）2つの条件（産地・商品名・月別売上）の合計を求めみましょう。

産地と月別売上の合計を求めていきます。

A12セルに「千葉」、B12セルに「11月売上」、C16セルに「じゃがいも」を入力します。

数式を以下のように入力します。

はい、縦横複数条件の合計が取得できましたね。

前の条件に商品名の条件を加えた形となります。条件を追加する場合は「*」を加えそのあとに条件式を入力します。

他の条件も設定してみましょう。

産地、売上月、商品名を入力し、数式をドラッグしていきます。

こちらも縦横複数条件の合計が取得できましたね。

部分一致の条件で合計を求める

条件（産地・商品名・月別売上）の項目はそのままに、商品名を完全一致から部分一致に変更して合計を求めみましょう。

産地と月別売上の合計を求めていきます。

A12セルに「千葉」、B12セルに「11月売上」、C16セルに「いも」を入力します。

数式を以下のように入力します。

はい、「いも」が含まれる商品名の合計が取得できましたね。

FIND関数は検索した文字が検索先の文字列の何番目にあるか返す関数で、「いも」が含まれる値の文字位置を取得します。

次に登場するISNUMBER関数は数字であればTrueを返す関数となります。FIND関数の結果が数字であればTrue、そうでなければFalseとなり、「いも」が含まれる値である場合はTrueとなり、SUMPRODUCT関数のカウント対象となるわけです。

タカヒロ

こちらも少々複雑ですね…
SUMPRODUCT関数は「*いも」などワイルドカード設定ができないのが悩ましいところですね。

他の条件も設定してみましょう。

産地、売上月、商品名「いも」を入力し、数式をドラッグしていきます。

こちらも複数条件の合計が取得できましたね。

～以外の条件で合計を求める

条件（産地・商品名・月別売上）の項目はそのままに、商品名を完全一致から一致しない否定に変更して合計を求めみましょう。

産地と月別売上の合計を求めていきます。

A24セルに「千葉」、B24セルに「11月売上」、C24セルに「じゃがいも」を入力します。

数式を以下のように入力します。

はい、「じゃがいも」以外の商品名の合計が取得できましたね。

これまで完全一致の等号「=」を使っていましたが、今回は逆の否定を表す「<>」にします。

他の条件も設定してみましょう。

産地、売上月、商品名を入力し、数式をドラッグしていきます。

こちらも「じゃがいも」以外の合計が取得できましたね。

～以上の条件で合計を求める

条件（産地・月別売上）の項目に売上で基準値以上である商品の合計を求めみましょう。

産地と月別売上で、売上が基準値以上である商品の合計を求めていきます。

A28セルに「千葉」、B28セルに「11月売上」、C28セルに「2000」を入力します。

数式を以下のように入力します。

はい、基準値の2000以上の売上がある商品の合計が取得できましたね。

今回は~以上を表す「>=」に変更し、取得する列を数値であるC列に変更します。

他の条件も設定してみましょう。

産地、売上月「12月売上」、基準値を入力し、数式をドラッグしていきます。

次に12月売上が対象となるので取得する列をD列に変更します。

こちらも12月売上の中で2000以上の売上がある商品の合計が取得できましたね。

SUMPRODUCT関数の条件に指定できる不等号/等号

SUMPRODUCT関数の条件に指定できる不等号/等号は以下通りです。

不等号

タカヒロ

不等号で条件を指定する場合は計算できる数値である必要があります。日付はWクリックして数字のシリアル値に変換されるか確認をしましょう。

等号

SUMPRODUCT関数で「#VALUE!」エラーとなった場合の原因と回避方法

SUMPRODUCT関数で「#VALUE!」エラーとなった場合の原因と回避方法について説明をします。

SUMPRODUCT関数「#VALUE!」エラーの原因

以下の場合にエラーとなります。

SUMPRODUCT関数「#VALUE!」エラーの回避方法

①の場合は別シートや別ブック名が存在するものに訂正をしましょう。ほとんどがスペルミスなど手打ちによる誤入力が原因になります。

②の場合はSUMPRODUCT関数に複数している参照範囲の数字の部分を一致させるようにしましょう。
例えばA1:A5、B2:B3の場合はA1:A5、B1:B5などどちらかの範囲数値を一方のほうにそろえるようにします。

VBAでSUMPRODUCT関数を一括挿入する

これまで関数を手入力する必要がありましたが、次はVBAを使い、関数を挿入する処理や計算結果を挿入する処理を一括で行っていきましょう。

タカヒロ

ここからこの記事のメインとなります。

FormulaでSUMPRODUCT関数を数式のまま挿入する（アドレス指定）

Formulaを使い、SUMPRODUCT関数を数式のまま一括挿入してみましょう。

表は縦横複数条件指定の表を使います。

ワークシート関数版と同じく、産地、売上月別の合計値を算出、代入していきます。

VBAは以下の通りです。

Sub SUMPRODUCT関数を一括挿入する1()
    For i = 1 To 2
        Cells(11 + i, 4).Formula = "=SUMPRODUCT((A2:A9=" & Cells(11 + i, 1).Address & ")*(C1:D1=" & Cells(11 + i, 2).Address & "),C2:D9)"
    Next
End Sub

For文で2回処理を繰り返します。

Cellsオブジェクトの行、列番号指定により入力先のセルを指定しFormulaプロパティで数式形式で代入します。

Formulaはその名の通り数式を入れるプロパティとなります。
値として受け渡す情報は文字情報でしかないですが、Formulaを使うことにより数式としてセルへ代入をしてくれます。

CellsオブジェクトのAddressプロパティで参照先セルの位置情報を取得し、SUMPRODUCT関数の引数に割り当てています。

VBAを実行してみましょう。

はい、数式のまま挿入できましたね。

FormulaでSUMPRODUCT関数を数式のまま挿入する（値指定）

続いて、条件をセルのアドレスからセルの値である文字列となるよう変更してみましょう。
VBAは以下の通りです。

Sub SUMPRODUCT関数を一括挿入する2()
    For i = 1 To 2
        Cells(11 + i, 4).Formula = "=SUMPRODUCT((A2:A9=""" & Cells(11 + i, 1).Value & """)*(C1:D1=""" & Cells(11 + i, 2).Value & """),C2:D9)"
    Next
End Sub

CellsオブジェクトのAddressプロパティをValueプロパティへ変え、セルの値を直接数式に入れるようにしています。

VBAを実行してみましょう。

はい、条件がセルのアドレスから文字列に変わりましたね。

SUMPRODUCT関数の結果を挿入する

SUMPRODUCT関数の結果を挿入してみましょう。
VBAは以下の通りです。

Sub SUMPRODUCT関数の結果を一括挿入する3()
    For i = 1 To 2
        strShiki = Evaluate("(A2:A9=" & Cells(11 + i, 1).Address & ")*(C1:D1=" & Cells(11 + i, 2).Address & ")")
        Cells(11 + i, 4).Value = WorksheetFunction.SumProduct(strShiki, Range("C2:D9"))
    Next
End Sub

For文で2回処理を繰り返します。

ワークシート関数をVBAで扱うにはWorksheetFunctionを使います。

Rangeオブジェクトで範囲指定、とCellsオブジェクトのValueプロパティで検索文字列を取得しSUMPRODUCT関数の引数に割り当てています。

SUMPRODUCT関数で使用した条件式「(条件1)*(条件2)」ですが、WorksheetFunction.SumProductでその方式で条件指定すると、

「実行時エラー13」、「型が一致しません」と表示されます。

条件式「(条件1)*(条件2)」はExcelシート上のSUMPRODUCT関数では有効ですが、VBAのWorksheetFunction.SumProductでは無効のようです。

そこで対策として、VBAでもExcelシートの数式をそのまま使えることができるEvaluateメソッドを採用しています。

Excelシート版SUMPRODUCT関数の条件式をEvaluateメソッドでくくることによりVBAでも条件式として認識されるという訳です。

VBAを実行してみましょう。

はい、SUMPRODUCT関数の結果が出力できましたね。

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。