Excelで条件付き合計を求めるSUMIF関数の使い方！複数条件の他VBAの一括設定も！

Excelで条件指定による合計をしたいときはないでしょうか。

そんな時に便利なのはSUMIF関数です。この関数1つで条件設定と合計を同時に実行してくれます。

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

ですよね。

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

についてまとめます！

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

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

SUMIF関数とはその名前からわかる通り、合計のSUM関数と条件設定のIF関数を組み合わせたもので、指定した条件による合計を返す関数です。

例えばこんな表があり、

指定範囲の中でキーワードを検索し一致した値の合計値を返します。

またSUMIFS関数を使うことにより複数条件による合計もできます。

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

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

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

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

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

サンプルは以下のようA列を売上日、B列を産地、C列を商品名、D列を売上金額にしました。

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

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

SUMIF関数で条件に一致した値の合計を取得する

条件に文字列を指定する

SUMIF関数で文字列に一致した値を合計を取得してみましょう。

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

B14セルにある値「千葉」を指定範囲から検索し、合致したD列の値を合計していきます。

D列までドラッグしていきます。

はい、文字列に一致した値の合計がとれましたね。

次は商品名ごとの合計を取得してみましょう。

商品名のひとつである「にんじん」をB23セルへ入力します。

数式は以下のようになります。D23セルへ入力します。

条件範囲は商品名なのでC列に変更します。

合計がとれましたね。

ほかの商品名もいれてみましょう。

数式をD26セルまでドラッグします。

はい、他の商品名の合計もとれいていますね。

条件に日付を指定する

次は日付を条件に指定してみましょう。

B18セルに「2021/11/27」を入力します。

数式は以下のようになります。D18セルに入れてください。

条件範囲は日付なのでA列に変更します。

合計がとれましたね。

数式をD20セルまでドラッグします。

はい、文字列に一致した値の合計がとれましたね。

タカヒロ

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

SUMIFS関数で複数の条件に一致した値の合計を取得する

条件に2つの文字列を指定する

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

条件に2つの文字列を指定してみましょう。
サンプルでは日付と商品名を対象にし、日付と商品名が合致した値の合計を求めていきます。

B29セルに「2021/11/27」、C29セルに「じゃがいも」を入力します。

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

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

他の日付でも設定してみましょう。

日付、商品名を入力し、数式をドラッグしていきます。

こちらも合計が取得できていますね。

条件に~以上を指定する

SUMIFS関数で複数の条件の内ひとつが～以上である場合の合計を取得してみましょう。

B33セルに「2021/11/28」、C33セルに「じゃがいも」を入力します。

数式は以下のようになります。

条件の文字列に不等号を加えています。

~以上は「>=」で表現します。

はい、売上日が11/28以降の指定商品ごとの合計がとれましたね。

他の商品名でも試してみましょう。

こちらも合計がとれていますね。

不等号の指定パターンとしては、不等号をセルの値に直接記載してもよいですし、

数式に直接入力しても同様の結果を得ることができます。

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

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

不等号

タカヒロ

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

等号

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

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

SUMIF関数/SUMIFS関数「#VALUE!」エラーの原因

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

SUMIF関数/SUMIFS関数「#VALUE!」エラーの回避方法

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

②の場合は文字列に指定してる文字数を255文字以下にしましょう。

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

VBAでSUMIF関数/SUMIFS関数を一括挿入する

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

タカヒロ

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

FormulaでSUMIF関数/SUMIFS関数を数式のまま挿入する

Formulaを使い、数式のままで挿入をしてみましょう。

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

サンプルの表は関数の時と同じものを使用します。

SUMIF関数を数式のまま挿入する

SUMIF関数を数式のまま一括挿入してみましょう。
VBAは以下の通りです。

Sub SUMIF関数を一括挿入する1()
    For i = 1 To 2
        Cells(13 + i, 4).Formula = "=SUMIF(B2:B11," & Cells(13 + i, 2).Address & ",D2:D11)"
    Next
End Sub

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

Cellsオブジェクトの行、列番号指定により入力先のセルを指定します。

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

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

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

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

Sub SUMIF関数を一括挿入する2()
    For i = 1 To 2
        Cells(13 + i, 4).Formula = "=SUMIF(B2:B11,""" & Cells(13 + i, 2).Value & """,D2:D11)"
    Next
End Sub

CellsオブジェクトのAddressプロパティをValueプロパティへ変えています。

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

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

SUMIFS関数を数式のまま挿入する

SUMIFS関数を数式のまま一括挿入してみましょう。
VBAは以下の通りです。

Sub SUMIFS関数を一括挿入する1()
    For i = 1 To 2
        Cells(17 + i, 4).Formula = "=SUMIFS(D2:D11,A2:A11," & Cells(17 + i, 2).Address & ",C2:C11," & Cells(17 + i, 3).Address & ")"
    Next
End Sub

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

Cellsオブジェクトの行、列番号指定により入力先のセルを指定します。

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

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

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

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

Sub SUMIFS関数を一括挿入する2()
    For i = 1 To 2
        Cells(17 + i, 4).Formula = "=SUMIFS(D2:D11,A2:A11,""" & Cells(17 + i, 2).Value & """,C2:C11,""" & Cells(17 + i, 3).Value & """)"
    Next
End Sub

CellsオブジェクトのAddressプロパティをValueプロパティへ変えています。

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

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

SUMIF関数/SUMIFS関数の結果を挿入する

次は、SUMIF関数/SUMIFS関数の結果を挿入をしてみましょう。

タカヒロ

関数入力後の演算処理が省略できますので、処理で重くなったと感じた場合の対策に有効ですね。

SUMIF関数の結果を挿入する

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

Sub SUMIF関数の結果を一括挿入する3()
    For i = 1 To 2
        Cells(13 + i, 4).Value = WorksheetFunction.SumIf(Range("B2:B11"), Cells(13 + i, 2).Value, Range("D2:D11"))
    Next
End Sub

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

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

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

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

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

SUMIFS関数を数式のまま挿入する

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

Sub SUMIFS関数の結果を一括挿入する3()
    For i = 1 To 2
        Cells(17 + i, 4).Value = WorksheetFunction.SumIfs(Range("D2:D11"), Range("A2:A11"), Cells(17 + i, 2).Value, Range("C2:C11"), Cells(17 + i, 3).Value)
    Next
End Sub

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

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

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

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

VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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

さいごに

いかがでしょうか。

今回は、

についてまとめました。

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