Excel(エクセル)のSUMIFS関数を使うことで、複数の条件に一致するセルの値を合計することができます。
しかし、SUMIFS関数を普通に利用すると、OR条件(または)では利用できません。
それは、SUMIFS関数はAND条件(なおかつ)で使用できる仕様になっているからです。
ただ、SUMIFS関数をOR条件で利用する裏テクが存在します。
この記事では、SUMIFS関数とSUM関数を使って、OR条件で合計値を求める方法を紹介します。
また、DSUM関数というOR条件で合計値を算出できる便利な関数も合わせて紹介します。
SUMIFS関数の使い方(おさらい）
SUMIFS関数の使い方をおさらいしましょう。
SUMIFS関数は複数の条件に一致する行を検索し、合計対象範囲の値を合計する関数です。
＝SUMIFS(合計範囲,条件範囲１,条件１,条件範囲２,条件２, ・・ ）
SUMIF関数に複数のSが付いていることからも分かるとおり、SUMIF関数との違いは複数の検索条件（最大127個）を設定できる点です。
SUMIFS関数を使ったAND条件の実例
SUMIFS関数をAND条件で使った実例を１つ紹介します。
下のSUMIFS関数を利用することで、表のC列が「B製品」で、かつ、D列が「南区」の販売数量を合計することが出来ます。
=SUMIFS(E3:E10,C3:C10,"B製品",D3:D10,"南区")
こんなふうに、SUMIFS関数はAND条件で使うのが基本だよ。
SUMIFS関数の使い方をさらに詳しく確認したい方は、以下の記事を参考にしてください。
SUMIFS関数をOR条件で使おうとしても、結果は「0」になる
SUMIFS関数で同じ列から複数の条件のものを集計しようとするとどうなるでしょうか。
下の表のC列から「A製品」と「B製品」の合計を集計するために、下のSUMIFS関数を挿入しても、SUMIFS関数はAND条件(なおかつ)でしか利用できない(一致するものが存在しない)ので、結果は「0」となります。
=SUMIFS(D3:D10,C3:C10,"A製品",C3:C10,"B製品")
A製品とB製品の数量を合計するためには、下の数式のように、SUMIF関数を２つ利用してA製品とB製品の合計を足し算しますが、どうしても関数が長くなってしまいます。
=SUMIF(C3:C10,"A製品",D3:D10)+SUMIF(C3:C10,"B製品",D3:D10)
このように、SUMIFS関数はAND条件で利用するために作られている関数なので、OR条件は基本的に対応できません。
しかし、以下の２つのどちらかの手段を用いることで、OR条件に一致する値を合計することが可能となります。
OR条件で合計する方法
方法１．SUM関数とSUMIFS関数を利用する
方法２．DSUM関数を利用する
SUMIFS関数とSUM関数でOR条件で合計する方法
それでは、今回の本題のテーマである、OR条件に当てはまる値を合計する方法を紹介します。
まずは、SUMIFS関数とSUM関数を使って、同じ列にOR条件の複数条件で合計を求める方法を紹介します。
説明用に、以下の表のC列が「A製品もしくはB製品」の数量を合計します。
手順１．SUM関数を挿入
まずは、数式を挿入したいD12セルにSUM関数を挿入し、「=SUM(」と入力します。
=SUM(
手順２．SUM関数の中にSUMIFS関数を挿入
続いて、SUM関数の中にSUMIFS関数を挿入するために、続いて「SUMIFS(」と入力します。
=SUM(SUMIFS(
このように、関数の中に関数を挿入するテクニックを入れ子(ネスト）といいます。
手順３．SUMIFS関数の第１引数を指定
続いて、SUMIFS関数の第１引数「合計範囲」を指定します。
手順２の数式に続き、「D3:D10,」とD列のセル範囲を指定します。
=SUM(SUMIFS(D3:D10,
手順４．SUMIFS関数の第２引数を指定
さらに、SUMIFS関数の第２引数「条件範囲１」を指定します。
手順３の数式に続き、「C3:C10,」とC列のセル範囲を指定します。
=SUM(SUMIFS(D3:D10,C3:C10,
手順５．SUMIFS関数の第３引数に配列を指定
この手順５が最も重要です。
通常であれば、SUMIFS関数の第３引数は１つだけの条件を設定しますが、今回は、配列の形式を使って、『{"A製品","B製品"}』と入力して、{}を使って２つの条件を一緒に指定します。
=SUM(SUMIFS(D3:D10,C3:C10,{"A製品","B製品"}))
SUM関数とSUMIFS関数をそれぞれ『)』で閉じる必要があるので、数式の最後は、『)』が２つ必要です。
手順６．SUM関数とSUMIFS関数を組み合わせた結果
「Enter」キーを使って関数を確定させてください。
そうすると、C列がA製品とB製品の合計数量が、D12セルに表示されました。
SUMIFS関数とSUM関数でOR条件で合計(実例)
上で紹介した例では『A製品またはB製品』という２つのOR条件で合計する方法を紹介しました。
同じ方法を用いることで、３つや４つの複数条件でも合計することが可能です。
下の表のように、SUMIFS関数の第3引数に「{"A製品","B製品","C製品"}」と入力することで、３つの条件の合計値を算出することができます。
=SUM(SUMIFS(D3:D10,C3:C10,{"A製品","B製品","C製品"}))
SUM関数を除いてOR条件を求めた結果
上で紹介した例では、SUM関数とSUMIFS関数を組み合わせる入れ子(ネスト）を利用しましたが、SUM関数を除くとどうなるか紹介します。
『A製品またはB製品』の合計を求めるために、以下の数式を挿入します。
=SUMIFS(D3:D10,C3:C10,{"A製品","B製品"})
そうすると、A製品の合計「300」はD12セルに表示され、B製品の合計「1000」は隣のセルのE12セルに表示されてしまいます。
そのために、SUM関数とSUMIFS関数は一緒に組み合わせて利用するようにしましょう。
DSUM関数を使うとOR条件で合計するのが簡単
SUM関数とSUMIFS関数を使わなくても、DSUM関数(ディーサム)を使うことでOR条件で一致する値を合計することができます。
DSUM関数は、OR条件でもAND条件でも、それらを組み合わせた複合条件でも合計を求めることが出来る関数です。
DSUM関数は、以下の構文となっていて、「データベース」「フィールド」「検索条件」の３つの引数を設定します。
＝DSUM(データベース, フィールド, 検索条件)
表から「A製品もしくはB製品」の売上数量を合計する場合は、以下のように関数を作成することで、D12セルに２つの条件の合計を表示させることができます。
=DSUM(B2:D10,D2,F7:F9)
DSUM関数の特徴は、条件表(F7:F9)を表の枠外に作成するところだよ。
DSUM関数の使い方は、以下の記事で紹介しているので、興味のある方は参考にしてください。
