SUMIFS関数で複数のセル参照値からOR条件で合計を出す方法

midSun31midSun31
IT・デジタル

SUMIFS関数は複数の検索条件をAND条件で合計を出す関数です。しかし、実際にはAND条件だけでなくOR条件で合計を出したいケースもありますよね。

この記事では、Excel/スプレッドシートのSUMIFS関数で複数のセル参照値からOR条件で合計を集計する方法をご紹介します。例では2つのOR条件、3つのOR条件で合計を出す方法を解説します。

2つのOR条件で合計を出す場合

SUMIFS関数でOR条件を使って合計を出すには、条件を個別に集計した合計値から重複した合計値を差し引くことで計算できます。数式は次のとおりです。

=SUMIFS(合計範囲, 条件範囲1, 条件1) + SUMIFS(合計範囲, 条件範囲2, 条件2) - SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2)

それぞれ個別の条件の合計値を足すと、「条件1 かつ 条件2」の2倍の合計値が加算されます。

そのため、「条件1 かつ 条件2」の合計値を差し引くことで「条件1 または 条件2」の合計値を算出することができます。

2つのOR条件で合計を出す例

具体的な例を挙げて解説していきます。下の表で「売上日=2019/09/03 または エリア=名古屋」のOR条件で売上金額の合計を算出します。

具体的な数式の組み立て方は次のとおりです。

手順1:単体の条件を満たすセルを合計する

先に単体の条件である「売上日=2019/09/03」と「エリア=名古屋」をSUMIFS関数を使ってそれぞれ合計を出します。

単体の条件なので、数式はSUMIF関数でも問題ありません。

  • 条件1の合計値を出す
    条件1の「売上日=2019/09/03」を満たすセルは以下のように集計できます。
    SUMIFS(I3:I12,F3:F12,"=2019/09/03")

    合計対象範囲が「売上金額」なのでI列を選択し、参照範囲は「売上日」なのでF列を選択します。

  • 条件2の合計値を出す
    条件2の「エリア=名古屋」を満たすセルを同様に集計します。

    SUMIFS(I3:I12,G3:G12,"名古屋")

手順2: 条件1と条件2の合計値を足す

複数の条件の合計値を足します。次の数式で「売上日=2019/09/03」と「エリア=名古屋」を満たすセルの合計が算出されます。

=SUMIFS(I3:I12,F3:F12,"=2019/09/03")+SUMIFS(I3:I12,G3:G12,"名古屋")

しかし、上記の数式の合計値は「60000」となり、本来の条件の合計値より「10000」大きくなってしまいます。

理由としては、「売上日=2019/09/03 かつ エリア=名古屋」であるI8セルを2回集計してしまっていることが原因です。

手順3:重複している合計値を差し引く

最後に条件1と条件2で重複している合計値を差し引きます。

重複している条件「売上日=2019/09/03 かつ エリア=名古屋」の合計値は次の数式で求めることができます。

SUMIFS(I3:I12,F3:F12,"=2019/09/03",G3:G12,"名古屋")

上記の数式を条件1と条件2の合計値から差し引くことで、正しいOR条件の集計値が算出できます。

=SUMIFS(I3:I12,F3:F12,"=2019/09/03")+SUMIFS(I3:I12,G3:G12,"名古屋")-SUMIFS(I3:I12,F3:F12,"=2019/09/03",G3:G12,"名古屋")

「売上日=2019/09/03 または エリア=名古屋」なので、合計値「50000」を取得できれば計算完了です。

上記のとおり、重複した合計値が排除され「50000」を取得することができます。

3つのOR条件で合計を出す場合

3つ以上のOR条件でも同様の考え方でSUMIF関数を使って集計できます。3つの条件のケースでは、次の数式になります。

=SUMIFS(合計範囲, 条件範囲1, 条件1) + SUMIFS(合計範囲, 条件範囲2, 条件2) + SUMIFS(合計範囲, 条件範囲2, 条件2) + SUMIFS(合計範囲, 条件範囲3, 条件3)
- SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2) - SUMIFS(合計範囲, 条件範囲2, 条件2, 条件範囲3, 条件3) - SUMIFS(合計範囲, 条件範囲3, 条件3, 条件範囲1, 条件1)
+ SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, 条件範囲3, 条件3)

まず「それぞれの条件の合計値」から「重複した合計値」を差し引きます。最後に「不足したすべての条件の合計値」を足すことで、3つのOR条件の合計値を算出できます。

3つのOR条件で合計を出す例

サンプルの表で「売上日=2019/09/03 または エリア=名古屋 または 販売個数が24以上」のOR条件で集計する場合は、次の数式で算出できます。

=SUMIFS(I3:I12,F3:F12,"=2019/09/03")
+SUMIFS(I3:I12,G3:G12,"名古屋")
+SUMIFS(I3:I12,H3:H12,">=24")
-SUMIFS(I3:I12,G3:G12,"名古屋",H3:H12,">=24")
-SUMIFS(I3:I12,F3:F12,"=2019/09/03",G3:G12,"名古屋")
-SUMIFS(I3:I12,F3:F12,"=2019/09/03",H3:H12,">=24")
+SUMIFS(I3:I12,F3:F12,"=2019/09/03",G3:G12,"名古屋",H3:H12,">=24")

4つ以上のOR条件で合計を出す場合

SUMIFS関数をOR条件で合計を出す方法は、条件が多くなるほど式が複雑になります。

4つ以上のOR条件で合計を出す場合は、OR条件を判定するセルを別途設けるか、NOT条件(全体から反対の条件を除く)を使用することを検討した方が良いでしょう。

以上、SUMIFS関数で複数のセル参照値からOR条件で集計する方法でした。

IT・デジタル
Howpon[ハウポン]

コメント