ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.015

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

2003/2007/2010/2013

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

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

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

<完全一致のOR条件>

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

SUMIF

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

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関数の条件はワイルドカードを使用できないため、他の関数を利用しながら式を作ることもできますが、長くややこしくなってしまいますので省きます。

部分一致のOR条件合計

SUM、SUMIFなどを使った3例、2番目の逆転の発想の式がいいでしょうか。

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

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

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

データベース関数は、相変わらず簡潔ではあります。

  =DSUM(A1:B10,B1,I7:J9)

もっといろいろな式があるでしょうが、とりあえず今回はこんなところでお願いいたします。

twitter hatena line pocket

関連ヘルプ

新規メールが開くハイパーリンクをまとめて設定するには 《HYPERLINK》
重複データを調べる 《COUNTIF》
設定されているリンクを保ちながら、行と列を入れ替えて表を貼り付けたい 《配列数式/TRANSPOSE》
VLOOKUP関数の結果がエラー値の場合には空白で表示させる 《IFERROR・IF・ISERROR》
0を抜いた最小値を調べるには 《MIN・SMALL・COUNTIF・IF》
3つ以上ある範囲に条件分岐するには(多分岐) 《IF》
データに連番をふる、同じデータには同じ番号 《INDEX・MATCH・COUNTIF・MAX》
規則性がない空白セルを除いた行に連番をふるには 《IF・COUNTA・COUNTIFS》
複数条件を満たすデータの件数を求めるには 《COUNTIF・COUNTIFS・SUMPRODUCT》
奇数行の合計、偶数行の合計、n行おきの合計を求める 《配列数式/IF・SUM・MOD》
時刻を5分単位で切り上げまたは切り捨てて表示するには 《CEILING・FLOOR・ROUNDUP》
参照式の参照セルが移動しても固定されるようにしたい 《INDIRECT》
VLOOKUP関数で、複数テーブルから検索したい 《VLOOKUP・アンパサンド》
2つのデータを比較し、他方にない(両方にある)データを探す 《VLOOKUP・COUNTIF》
日付を表す8桁の数値データを、6桁の和暦にしたい 《DATE・TEXT》