No.015

OR条件に当てはまるデータの合計を求める《SUMIF・SUMIFS・SUMPRODUCT・DSUM》

2003/2007/2010/2013

AND条件の合計の場合は、Excel2007以降ではSUMIFS関数が用意されたり、他の方法でもスマートにできるのですが、OR条件となると下手な式を作ると間違った答えとなってしまいますので注意が必要です。

どちらにせよ一番着実な方法は、1つずつの条件の合計をSUMIF関数で求めてプラスしていく、もしダブりがある場合はその分をマイナスする方法です。

また、OR条件でもそれらの条件が完全一致のケースと部分一致のケースとでは作成する数式も違ってきます。
今回は、条件が完全一致のケースと部分一致のケースにおける、OR条件合計値の求め方をいくつかご紹介します。

＜完全一致のOR条件＞

簡単なデータで申し訳ないですが、列Aにおいて「あああ」または「いいい」を満たす列Bの合計を求めます。
条件に該当するのは色を付けた3件です。合計30です。

詳しい説明はいたしませんが、画像の式は上から次のとおりです。

SUM、SUMIFなどを使った3例ですが、1番目は素直な例、2番目は逆転の発想、3例目はおもしろい書き方です。

　　=SUMIF(A2:A10,"あああ",B2:B10)+SUMIF(A2:A10,"いいい",B2:B10)

　　=SUM(B2:B10)-SUMIFS(B2:B10,A2:A10,"<>あああ",A2:A10,"<>いいい")

　　=SUM(SUMIFS(B2:B10,A2:A10,{"あああ","いいい"}))

SUMPRODUCTなどを使った例は、理解しやすいものをお使いください。

　　=SUMPRODUCT((A2:A10="あああ")*B2:B10+(A2:A10="いいい")*B2:B10)

　　=SUMPRODUCT(((A2:A10="あああ")+(A2:A10="いいい"))*B2:B10)

　　=SUMPRODUCT((A2:A10={"あああ","いいい"})*B2:B10)

配列数式で求める場合は、中かっこ{ }を除いた式をたてて、最後に「Ctrl」＋「Shift」＋「Enter」で確定します。

　　{=SUM(IF(A2:A10="あああ",B2:B10,IF(A2:A10="いいい",B2:B10,"")))}

データベース関数で求める場合は、どこかに抽出条件を書いておく必要があります。

　　=DSUM(A1:B10,B1,I12:J14)

＜部分一致のOR条件＞

条件が部分一致となると、少し面倒になってきます。
特に部分一致条件を複数満たしてしまうようなデータがあるとなおさらです。

今回も簡単なデータですが、列Aで「あ」または「い」を含むデータの列B合計を求めます。
セルA5のデータは「あ」も「い」も両方入っていますので、その分を対応する必要性が出てきます。したがって、条件を満たすデータは5件です。
IF関数やSUMPRODUCT関数の条件はワイルドカードを使用できないため、他の関数を利用しながら式を作ることもできますが、長くややこしくなってしまいますので省きます。