ANDだけでなくOR条件も指定できる

条件に一致するデータを数える関数としてはCOUNTIF(カウント・イフ)関数が定番ですが、この関数では条件を1つしか指定できません。1つではなく、複数の条件でデータを数えられるのが、ここで解説するCOUNTIFS(カウント・イフ・エス)関数です。

まずはCOUNTIFS関数の構文を確認しましょう。

=COUNTIFS範囲1, 検索条件1, 範囲2, 検索条件2, ...)

引数[範囲]と[検索条件]を1つのセットとし、そのセットを複数指定することで、複数の条件に一致するデータの数を求められます。[範囲]と[検索条件]のセットは、最大で127組まで指定できます。

こうして指定した複数の条件は、通常はAND条件、つまり「AかつB」を満たすデータを数えることになります。一方で、単純な四則演算を使ってOR条件、つまり「AまたはB」の条件を指定することも可能です。例えば、以下のように複雑な条件も指定できます。

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

「かつ」のAND条件が基本ですが、「または」のOR条件も指定できます。

活用の幅が広いCOUNTIFS関数について、実際の使い方を見ていきましょう。

2つの条件に一致するデータを数える

ここでは2つの条件として「男性、かつ、来店回数が10回以上」を指定します。COUNTIFS関数のもっとも基本的な使い方です。数式は以下のようになります。

=COUNTIFS(C8:C27,"男",F8:F27,">=10")

引数[検索条件]で利用できる演算子については、本記事末尾のHINTを参照してください。「10回以上」の条件は「">=10"」と指定します。

1COUNTIFS関数を入力する

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

セルE1に「=COUNTIFS(C8:C27,"男",F8:F27,">=10")」と入力します。

2結果が表示された

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

「男性、かつ、来店回数が10回以上」の条件に一致するデータが数えられました。

なお、COUNTIFS関数の条件は、数式内ではなく別のセルで指定しても構いません。その場合は引数[検索条件]をセル参照で指定します。以下はセルG1に「男」、セルH1に「>=10」と入力してある場合の数式です。

=COUNTIFS(C8:C27,G1,F8:F27,H1)

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

セル参照で条件を指定しても結果は同じ「3」です。

1つの条件に一致するデータを数える

逆に、COUNTIFS関数で単一の条件を指定することもできます。もちろん、結果はCOUNTIF関数と一致します。

例えば、女性の会員だけを数えるなら、数式は以下になります。

=COUNTIFS(C8:C27,"女")

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

セルE2に「=COUNTIFS(C8:C27,"女")」と入力した結果です。COUNTIF関数を使って「=COUNTIF(C8:C27,"女")」としたときと同じです。

範囲内のデータを数える

続いて「来店回数が5回以上10回未満」の人を数えます。数式で考えると「10>(人数)>=5」となり、5回以上は「">=5"」、10回未満は「"<"」で表現できます。COUNTIFS関数の数式としては以下になります。

=COUNTIFS(F8:F27,">=5",F8:F27,"<10")

ここでは数値を比較していますが、「COUNTIFS(A:A,"<=yyyy/mm/dd")」のように日付の範囲を指定することも可能です。

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

セルE3に「=COUNTIFS(F8:F27,">=5",F8:F27,"<10")」と入力した結果です。

OR条件で数える(条件の重複なし)

「AまたはB」のOR条件を指定します。ここでは「来店回数が3回以下、または、10回以上」の条件を指定します。

3回以下は「"<=3"」、10回以上は「">=10"」です。この2つの範囲に重複はないので、COUNTIFS関数の数式を2つ用意し、数えた結果を足し合わせればOKです。

=COUNTIFS(F8:F27,"<=3") + COUNTIFS(F8:F27,">=10")

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

セルE4に「=COUNTIFS(F8:F27,"<=3") + COUNTIFS(F8:F27,">=10")」と入力した結果です。

この結果が本当に正しいのか、フィルターの結果と比較してみます。

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

フィルターオプションで『「3」以下、「10」以上』と指定します。

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

結果は「13」となりました。COUNTIFS関数で正しくカウントできていますね。

OR条件で数える(条件の重複あり)

最後に「重複あり」のOR条件を指定してみます。条件は「新宿店の会員、または、来店回数が5回以上」です。先ほどと同じように2つの数式を考えると、

=COUNTIFS(E9:E28,"新宿") + COUNTIFS(F9:F28,">=5")

となりますが、このままでは「新宿店の会員で5回以上来店」の人を2回カウントしてしまいます。

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

セルE5に「=COUNTIFS(E9:E28,"新宿") + COUNTIFS(F9:F28,">=5")」と入力した結果です。結果は「14」となりました。

フィルターを適用して確認してみましょう。まずは「新宿店の会員」です。

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

[店舗]列で「新宿」を抽出しました。結果は7人です。5回以上来店が3人いますね。

続けて「5回以上来店」のフィルター結果です。

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

[来店回数]列で「5以上」を抽出しました。結果は7人です。新宿店の会員が3人います。

「新宿店」の抽出結果7人と「5回以上」の抽出結果7人を単純に足すと、「新宿店の会員、かつ、5回以上来店」の3人がダブっていますよね。つまり「新宿店の会員、かつ、5回以上来店」を以下の数式で求めて、

=COUNTIFS(E9:E28,"新宿",F9:F28,">=5")

これを最初の数式から引けばいいわけです。まとめると、以下の数式になります。

=COUNTIFS(E9:E28,"新宿") + COUNTIFS(F9:F28,">=5") - COUNTIFS(E9:E28,"新宿",F9:F28,">=5")

COUNTIFS関数で複数条件に一致するデータを数える。OR条件の指定も可能

セルE5の関数式を「=COUNTIFS(E9:E28,"新宿") + COUNTIFS(F9:F28,">=5") - COUNTIFS(E9:E28,"新宿",F9:F28,">=5")」と修正した結果です。正しく11人となりました。

以上、COUNTIFS関数の使い方を解説しました。

上述の通り、条件の指定は1つでも構いません。また、引数に利用できる比較演算子やワイルドカードはCOUNTIF関数と共通なので、常にCOUNTIFS関数のみを使っても問題はありません。

HINT引数[検索条件]に使える比較演算子

引数[検索条件]では、比較演算子とワイルドカードを使うことができます。指定する条件と組み合わせて、「"」(ダブルクォーテーション)で囲みます。

なお、ワイルドカード自体を検索したいときは、「*」や「?」を「~」で挟んで、「"~?~"」と指定してください。

引数[検索条件]に使える比較演算子

演算子 名称 使用例 結果
= 等しい "=5" 5と等しい
<> 等しくない "<>渋谷" 渋谷に等しくない
> より大きい ">5" 5より大きい
< より小さい "<5" 5より小さい
>= 以上 ">=5" 5以上
<= 以下 "<=5" 5以下

引数[検索条件]に使えるワイルドカード

ワイルドカード 意味 使用例 結果の例
* 任意の文字列 "新*" 新宿、新橋、新大久保
? 任意の1文字 "?田" 新田、本田、行田