SUMPRODUCT関数で複数条件満たすセルをカウントする
ここでは、SUMPRODUCT関数で「複数条件を全て満たすセルをカウントする方法」について解説します。
動きはCOUNTIFS関数と同じなのですが、COUNTIFS関数ではエラーになって、SUMPRODUCT関数ではエラーにならないケースがあります。
なのでSUMPRODUCT関数も覚えると、とても便利になると思いますよ。
１．COUNTIFS関数との違い
複数条件でカウントしたい場合、COUNTIFS関数とSUMPRODUCT関数のどちらでも可能ですが、それぞれメリット・デメリットがあります。
その中でおすすめは、「SUMPRODUCT関数」です。
SUMPRODUCT関数はCOUNTIFS関数よりも良い所が２つあり、次の通りです。
１－１．別ブックを参照してもエラーにならない
SUMPRODUCT関数の一番いいところは「別ブック（ファイル）を参照してもエラーにならない」ことです。
「別ファイルを参照する」とは、「数式を入れたファイルと値を参照するファイルが異なる」場合のことです。
COUNTIFS関数は別ファイルを参照すると、そのファイルが閉じている場合には下のようにエラーになります。
一方、SUMPRODUCT関数ではエラーにならないので、別ファイルのデータを複数条件でカウントしたい場合には、SUMPRODUCT関数を使った方がいいです。
ちなみに、COUNTIFS関数でも「その都度参照先のファイルを開く」ことでエラーは解消されますが、正直いって面倒です。
１－２．条件が指定しやすい、分かりやすい
SUMPRODUCT関数のいいところの二つ目は、「条件が指定しやすい、分かりやすい」ことです。
特に、「以上」や「以下」としたい時に顕著です。
例えば、「セルA1～A10」で「セルB1の値以上」をカウントしたい場合、数式はそれぞれ次のようになります。
COUNTIFS関数で条件を「以上」とする場合、上のように「">="&」という書き方が必要です。
対してSUMPRODUCT関数は、そのまま「>=」です。
このことから、条件を指定しやすい方がいい、分かりやすい方がいいといった時には、COUNTIFS関数よりもSUMPRODUCT関数の方がいいのです。
１－３．ただしデメリットもあります
ただし、SUMPRODUCT関数にはデメリットもあります。
それは、「何を求めているのかが分かりづらい」ところです。
まず、「SUMPRODUCT関数ってなに？」って思ってしまう人が多いです。
また、SUMPRODUCT関数は数式の書き方によって「複数行の乗算の合計」を求めることもできますので、一見して何を求めているのかが分かりづらいのです。
１－４．デメリットを踏まえて使い分けを
以上のことから、COUNTIF関数とSUMPRODUCT関数を使い分けるのが一番いい方法です。
「別ブックを参照する時」や「条件指定のしやすさ」を求める時にはSUMPRODUCT関数を、「見た目の分かりやすさ」を求める時にはCOUNTIF関数を使うのがいいでしょう。
なお、SUMPRODUCT関数の基本的な使い方は次のページで詳しく解説しています。
２．複数条件をカウントする方法
では、SUMPRODUCT関数で複数条件をカウントできる数式の作り方を見ていきます。
２－１．数式の形
まず、数式の形は次の通りです。
このように、カッコで囲った条件式同士を「*」でつなぐことで、すべての条件に合うセルをカウントすることができます。
例えば、条件が2つの時には次の通りです。
また、3つの時は次の通りです。
２－２．数式の例
一つ例を見てみましょう。
上の表は、研修参加実績の一覧です。研修名と参加日が入力されています。
この表をもとに、6月1日から7月31日までに参加した研修をカウントしたいとすると、セルG5に入れる数式は次の通りです。
=SUMPRODUCT(($C$3:$C$7>=G3)*($C$3:$C$7<=I3))
なお、この中で条件式は、
$C$3:$C$7>=G3
と
$C$3:$C$7<=I3
です。
２－３．条件式の書き方とポイント
まず、条件式は次の順番で書きます。
範囲 ⇒ 比較演算子 ⇒ 条件値
例えば、「範囲」を「セルC3からC7」、「比較演算子」を「>=」、「条件値」を「G3に入っている値」とする場合、条件式は次のようになります。
C3:C7>=G3
このように、「範囲」⇒「比較演算子」⇒「条件値」の順で書きます。
そして、条件式を書く時のポイントは4つあります。
①「範囲」は「1列（横の範囲）複数行（縦の範囲）」にする
範囲は、「1列複数行」にします。
列とはA、B、C…などの横の範囲のことです。行とは1、2，3…などの縦の範囲のことです。
そして、SUMPRODUCT関数では「横は1つだけ、縦は複数指定」します。
範囲を「B3:C7」のように複数列指定すると、#VALUE!エラーになります。
②「範囲」ごとの「行番号を一致」させる
「行番号を一致させる」とは、「縦の範囲を合わせる」ということです。
例えば条件式1が3～7行の範囲であれば、条件式2も3～7行目にします。
③ 「条件値」は型に合った書き方にする
セル番号と数値は「そのまま」、文字列や日付、時刻は「『"』で囲う」ようにします。
このように、条件値は型にあった書き方が必要です。
④ 「条件式」ごとに「カッコで囲う」
「(B3:B7=1)」、「(B3:B7=A1)」というようにカッコで囲います。
これらのことを守ると、正しい条件式が作れます。
２－４．比較演算子は以下の６種類
比較演算子は、以下の６種類あります。
| 演算子 | 意味 | 指定の例 |
| = | 等しい | =2 |
| > | 超える | >2（2を超える） |
| < | 未満 | <2（2未満） |
| >= | 以上 | >=2（2以上） |
| <= | 以下 | <=2（2以下） |
| <> | 等しくない（以外） | <>2（2以外） |
ちなみに、最後の「<>」は「以外」という条件にしたい時に使います。
全部覚えて、様々な条件が指定できるようになりましょう。
３．複数条件の数式を作ってみる
続いて、数式の作り方を例を挙げてみてみます。
先ほどの例を使って、「6月1日から7月31日までに参加した研修」をカウントしてみます。
以下、数式の作り方です。
（１）セルに「=SUMPRODUCT(」と入力し、fxボタンを押す
（２）引数「配列１」に、「(一つ目の条件式)*(二つ目の条件式)」と入力する
※ 例の場合は($C$3:$C$7>=F3)*($C$3:$C$7<=H3)
（３）OKを押せば完了
入れた数式は、次の通りです。
=SUMPRODUCT(($C$3:$C$7>=F3)*($C$3:$C$7<=H3))
このように、期間を指定してのカウントも簡単です。
４．条件が一つの場合は形がちょっと違う
「条件が一つの場合」だけは、数式の形がちょっと違います。
４－１．数式の形
条件が一つの時の数式の形は、次の通りです。
=SUMPRODUCT((条件式)*1)
ポイントは、「条件式に『1』を掛ける」ことです。
これを付けないと正しい値が返ってこないので、注意してください。
４－２．なぜ1をかけるのか？
SUMPRODUCT関数は、条件式で指定した「範囲」のセルごとに条件判定し、その結果を「TRUE（真）」か「FALSE（偽）」で返します。
そしてその後、範囲内をSUM関数のように合計します。
しかし、TRUEやFALSEは数値ではないので合計できません。そして、TRUEやFALSEは1を掛けると1や0に変換されます。
このため、条件が一つの場合にのみ「1」を掛けるのです。
４－３．数式の作り方
では、条件が一つの場合も数式を作ってみましょう。
上の表から「研修名の列に『エクセル関数研修』がいくつあるか？」を数えてみます。
以下、数式の作り方です。
（１）セルに「=SUMPRODUCT(」と入力し、fxボタンを押す
（２）引数「配列１」にカッコでくくった条件式を入力し、その後に「*1」と入力する
※ 例の場合、条件式は($B$3:$B$7=E3)
（３）OKを押せば完了
数式は、次の通りです。
=SUMPRODUCT(($B$3:$B$7=E3)*1)
【口コミ・評判のいいExcel本の紹介】
５．COUNTIFS関数からの変換の仕方
最後に、COUNTIFS関数からSUMPRODUCT関数へ変換したい場合のやり方です。
５－１．引数の場所を対比してみる
まずは、COUNTIFS関数から見たSUMPRODUCT関数の引数の場所を確認します。
範囲、条件値の順番は一緒ですが、SUMPRODUCT関数は「カッコで囲む」、「*でつなぐ」といったように形が異なっています。
この点に注意しながら当てはめます。
なお、「以上」や「以下」などの場合、COUNTIFS関数では「">="&条件値」となっています。
その場合には、SUMPRODUCT関数の「=」を「>=」に変える、といったように比較演算子も修正する必要があります。
５－２．例を確認
一つ例を見てみましょう。
上の例は、C列の参加日のうち「6月1日～7月31日まで」をCOUNTIFS関数でカウントしたものです。
数式は、次の通りです。
=COUNTIFS(C3:C7,">="&G3,C3:C7,"<="&I3)
これをSUMPRODUCT関数に変換すると、次のようになります。
=SUMPRODUCT((C3:C7>=G3)*(C3:C7<=I3))
対比すると、次のようになります。
このように数式の形を知っていれば、簡単に変換ができます。
以上、参考になれば幸いです。
SUMPRODUCT関数の、他の記事へのリンクはこちらです。
- SUMPRODUCT関数その他の記事へのリンクはこちら
- ◎基本的な使い方
- ◎複数条件満たすセルをカウント
- ◎複数条件の合計・集計が便利な理由
- ◎行ごとに掛け算した値の合計を求める