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

No.009

複数条件を満たすデータの件数を求めるには 《COUNTIF・COUNTIFS・SUMPRODUCT》

2003/2007/2010/2013

AND条件の複数条件を満たすデータの件数を求める方法です。

Excel2003以前はそのものの関数はありませんでした。しかしその要望はかなり多かったように思います。
Excel2007でついに、専用のCOUNTIFS関数が用意され、より簡単に求めることができるようになりました。

ここでまず、複数条件を満たすデータの件数を求める場合の方法を確認しておきましょう。ちなみに、COUNTIF関数の条件設定の引数は1つです。

  • COUNTIF関数(準備列用意)
  • COUNTIFS関数(Excel2007以降)
  • SUMPRODUCT関数
  • DCOUNT関数
  • COUNT関数とIF関数を使用した配列数式

では、上から順番にご案内いたします。最後2つの方法は簡単なご案内です。

<COUNTIF関数(準備列用意)>

COUNTIF関数(関数の分類: 統計)で求めることができるのは多少限定的ですが、下図のように、[F1]列と[F2]列のデータの組み合わせがそれぞれ何件あるか、といった場合に、条件となるデータをつなげた準備列を用意します。

準備列の式は、アンパサンド(&)を使用すると便利です。

  =A2&B2

COUNTIF関数の引数[範囲]に準備列を指定し、引数[検索条件]もアンパサンドを使用して求めます。

  =COUNTIF($D$2:$D$15,F3&G3)

COUNTIF関数

もちろん、準備列を作成するときにIF関数を用いると、より複雑な条件での件数を求めることができます。

以下は、上記の準備列を作れないときに使える方法です。

<COUNTIFS関数>

[F1]列が「あ」で、かつ[F3]列が「180以上」という複数条件で、件数を求めます。

COUNTIFS関数(関数の分類: 統計)は、COUNTIF関数の要領で複数セットの条件を設定して求めることができます。

  =COUNTIFS($A$2:$A$15,E4,$C$2:$C$15,">="&$F$3)

COUNTIFS関数

今回、条件「180以上」の180がセルF3に入力されていますので、大なり小なりの条件とセル参照をアンパサンド(&)でつないで指定しています。

<SUMPRODUCT関数>

SUMPRODUCT関数(関数の分類: 数学/三角)は、本来の使用方法でない少し変わった使用方法となります。

SUMPRODUCT関数

  =SUMPRODUCT(($A$2:$A$15=E4)*($C$2:$C$15>=$F$3))

SUMPRODUCT関数のこの使用方法は、かっこ()の使い方をよく見てください。
関数自体のかっこが最初と最後にあり、その中に条件が設定されているかっこが2つ、乗算の*が間にあります。
この計算は、それぞれのかっこ内のセル範囲のセル1個ずつで条件を評価し、条件を満たしたら1、満たさなければ0を返します。そしてセル範囲の対応するセル同士の条件評価(1または0)を掛け合わせ、最後に合計します。
つまり、複数の条件を満たすとその行で1となり、満たさないと0となります。その行の結果を最後合計しますので、結果的に複数条件を満たした件数となります。
この式を1つ目の引数のみに指定しますので、手入力の方が早いと思います。

Excel2003までは、COUNTIFS関数がなかったため、このSUMPRODUCT関数がよく利用されましたので、古いブックに見つけることがあるかもしれません。

以下の方法は簡単にご案内です。

<DCOUNT関数>

DCOUNT関数(関数の分類: データベース)では、条件の設定を別セルに用意する必要があります。使用方法は下図を参考にしてください。

DCOUNT関数

  =DCOUNT(A1C15,,I2:J3)

DCOUNT関数の場合、2つ目の引数[フィールド]は省略することができます。

<COUNT関数とIF関数を使用した配列数式>

COUNT関数またはSUM関数とIF関数を使用した配列数式で求めることもできます。
配列数式ですので、最後の確定は「Ctrl」+「Shift」+「Enter」を押します。
式の前後の中かっこは、確定後に表示されます。

配列数式

上図の[関数の引数]ダイアログ ボックスは、最初のIF関数の設定画面で、真の場合にIF関数がネストされています。

  {=COUNT(IF($A$2:$A$15=E4,IF($C$2:$C$15>=$F$3,1,""),""))}

ネストされた中のIF関数の真の場合、つまり2つのIF関数の条件を両方満たした場合は、1を返すようにしています。それ以外の偽の場合は0です。
赤い枠のところが、2つのIF関数の結果で、それらをCOUNT関数で数えます。空白はカウントしませんので、答えは4、複数条件を満たした件数です。
SUM関数でもいけますね。

配列数式は、配列(セル範囲)をまとめて計算に処理させる手法です。

ヘルプmemo

<COUNTIFS関数 (統計関数)>

特定の条件に一致するセルの個数を返します。
 数式: =DATEDIF(検索条件範囲1, 検索条件1, [検索条件範囲2, 検索条件2], ...)
 引数:
  検索条件範囲 …特定の条件による評価の対象となるセル範囲を指定
  検索条件 …計算の対象となるセルの条件を指定
 ヒント:
  最大127組の検索条件範囲と検索条件のペアを指定できます。

関連ヘルプ

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