【Excel】”SUMIFS”関数を使わずに複数条件付きの合計を求める方法
“SUMIFS"関数が存在しない、古いExcelを利用中の方。
“SUMIFS"関数が使いにくいと感じている方。
そんな方々は、是非この記事を読んでみてください。
複数条件付きの合計を求める方法
実は、Excelで「複数条件付きの合計を求める方法」には、いくつか種類があります。
ここでは、以下の3つ紹介したいと思います。
・"SUMIFS"関数
・"SUMPRODUCT“関数 （オススメ）
・"SUM"関数と"IF"関数の配列数式
それでは、各関数について、簡単に解説していきます。
“SUMIFS"関数 仕組みと使用例
始めに、複数条件付きの合計でよく用いられる、"SUMIFS"関数の仕組みと使用例を確認しましょう。
式は以下のような仕組みとなります。
=SUMIFS(合計対象範囲, 条件対象範囲 1, 条件 1, [条件対象範囲 2, 条件 2], …)
では、使用例を見ていきましょう。
日本プロサッカーリーグを用いた使用例では、以下の条件で「得失点差」の合計を求めてみます。
- 条件1：勝数が引分数以下
- 条件2：敗数が引分数以下
- 条件3：試合数が28試合
この際、"SUMIFS"関数では、比較演算（セル同士を比較する関係式）を、条件として式に直接組み込むことが出来ません。
その為、「勝数-引分数」(L列)と「敗数-引分数」(M列)を下準備として用意しています。
これにより、式を
=SUMIFS(J2:J21,L2:L21,“<=0",M2:M21,“<=0",D2:D21,28)
とすることで、複数条件付きの「得失点差」の合計を求めることが出来ました。
“SUMPRODUCT"関数 仕組みと使用例
続いて、この記事でオススメする、"SUMPRODUCT"関数の仕組みと使用例を確認しましょう。
“SUMPRODUCT"関数の既定の使用用途は乗算ですが、条件付きの加算にも用いることが出来ます。
その際、式は以下のような仕組みとなります。
=SUMPRODUCT((条件1)*(条件2)*…*合計対象範囲)
では、こちらも使用例を見ていきましょう。
今回も、先ほどと同じデータ、条件で「得失点差」の合計を求めます。
- 条件1：勝数が引分数以下
- 条件2：敗数が引分数以下
- 条件3：試合数が28試合
その際、"SUMPRODUCT"関数での式は以下のようになります。
=SUMPRODUCT((E2:E21<=F2:F21)*(G2:G21<=F2:F21)*(D2:D21=28)*J2:J21)
先ほどの"SUMIFS"関数との違いとしては、
・比較演算も条件として式に直接組み込むことができる。
・Excel 2002のような、古いバージョンでも使用できる関数である。
などが挙げられます。
使い勝手がいいので、個人的には一番オススメの関数です。
“SUM"関数と"IF"関数の配列数式 仕組みと使用例
最後に、ほとんど使うことはないと思いますが、"SUM"関数と"IF"関数の配列数式についても確認しましょう。
式は、以下のような仕組みとなります。
=SUM(IF((条件1)*(条件2)*…*, 合計対象範囲))
さらに、これを配列数式にしなくてはならないため、式の入力後、’Ctrl + Shift + Enter’を押す必要があります。
では、こちらも使用例を見ていきましょう。
最後もデータ、条件は同じものとして「得失点差」の合計を求めます。
- 条件1：勝数が引分数以下
- 条件2：敗数が引分数以下
- 条件3：試合数が28試合
まず入力する式は以下のようになります。
=SUM(IF((E2:E21<=F2:F21)*(G2:G21<=F2:F21)*(D2:D21=28),J2:J21))
その後、’Ctrl + Shift + Enter’を押すことで、（上の画像ではわかりにくいですが）式が {} で囲われます。
{}で囲われていれば、配列数式として正しく式が入力できていることになります。
※{}を手入力してしまうと、配列数式として認識されないので、必ず’Ctrl + Shift + Enter’を押す必要があります。
いかがだったでしょうか。
一般的には"SUMIFS"関数がよく使われますが、ぜひ、"SUMPRODUCT"関数も覚えておくと便利だと思います。