COUNTIFS関数で複数条件をOR（または）でカウントする
ここでは、エクセルのCOUNTIFS関数で複数条件の組み合わせを「OR（または）」でカウントする方法について解説します。
ここで言う「複数条件の組み合わせをORで」とは、「AND条件の組み合わせをOR条件で」のことで、例えば「『A and B』or『C and D』」などといった形です。
「AかBかC…」といったOR条件のみのカウントは、別のページで解説していますので、下記のリンクから参照してください。
１．複数条件を「または」でカウントする方法
ではさっそく、複数条件の組み合わせを「または」でカウントする数式を見てみましょう。
COUNTIFS関数を使った方法の他に、SUMPRODUCT関数を使った便利な方法も紹介します。
１－１．COUNTIFS関数を使った方法
まずは、COUNTIFS関数を使った方法からです。
上の表は、社員情報の一覧表です。
氏名や所属課、性別や出身が入力されています。
この表から「『男性』で『第一営業課』の人」か「『女性』で『経理課』の人」をカウントしてみます。
COUNTIFS関数で複数条件の組み合わせをORでカウントする時は、「COUNTIFS関数を足す」ことでできます。
例えば、例の数式は、
=COUNTIFS(C4:C13,"男",D4:D13,"第一営業課")+COUNTIFS(C4:C13,"女",D4:D13,"経理課")
です。
これは、数式の作り方を解説するまでもないでしょう。
しかしネックは「条件が増えた時に数式が長くなってしまう」ことで、例えば10個もOR条件にしたいと言った場合には、とても面倒です。
１－２．SUMPRODUCT関数を使った方法
そこで、条件がたくさんある時に便利なのが、「SUMPRODUCT関数を使った方法」です。
１－２－１．同様の条件でカウントした例
まずは、先ほどと同じ条件でカウントした例から見てみましょう。
先ほどと同じように「『男性』で『第一営業課』の人」か「『女性』で『経理課』の人」をカウントしており、数式は、
{=SUMPRODUCT((C4:C13=TRANSPOSE(G4:G5))*(D4:D13=TRANSPOSE(H4:H5)))}
となっています。
この数式のポイントは、「リスト化した条件を参照している」点で、条件が増えてもリストの範囲を増やすだけでOKなのがメリットです。
１－２－２．数式の作り方
では、数式の作り方を見てみましょう。
（１）数式バーに「=SUMPRODUCT((」と入力後、カウントしたい１つ目の範囲を指定する
例ではまず「性別」の範囲をカウントしたいので、数式は「=SUMPRODUCT((C4:C13」となります。
（２）「=TRANSPOSE(」と入力後、条件リストの１つ目の範囲を指定し、かっこ閉じを２つ付ける
例では一つ目の「条件リスト」をセルG4からG5までで作成したので、数式は「=SUMPRODUCT((C4:C13=TRANSPOSE(G4:G5))」となります。
（３）「*(」入力後、カウントしたい２つ目の範囲を指定する
例では「所属課」の範囲もカウントしたいので、数式は「=SUMPRODUCT((C4:C13=TRANSPOSE(G4:G5))*(D4:D13」となります。
（４）「=TRANSPOSE(」と入力後、条件リストの２つ目の範囲を指定し、かっこ閉じを３つ付ける
例では「条件リスト」をセルH4からH5までで作成したので、数式は「=SUMPRODUCT((C4:C13=TRANSPOSE(G4:G5))*(D4:D13=TRANSPOSE(H4:H5)))」となります。
（５）ShiftキーとCtrlキーを押しながらEnterキーを押せば完了
これでCOUNTIFS関数同士を足した数式と同じ結果が得られます。
２．COUNTIFS関数のOR条件はダブりに注意！
このように、SUMPRODUCT関数を使った方法はとても便利ですが、一方でCOUNTIFS関数を使った方法でしかできないこともあります。
あまり使わないかもしれませんが、それは「OR条件ごとに範囲を変えられる」ことです。
例えば、先ほどの例はOR条件のどちらも「性別」と「所属課」でした。
しかしCOUNTIFS関数であれば、一つ目は「性別」と「所属課」、二つ目は「所属課」と「出身」といった形にできるのです。
具体的には、「『男で総務課』か『第一営業で東京都』」といったカウントができます。
しかし、このケースでは一つ気を付けなければならないことがあります。それは「カウントのダブり」です。
ここでは、COUNTIFS関数のOR条件で起きるダブりとは何か、どう対処すればいいかも見てみます。
２－１．COUNTIFS関数のOR条件で起きるダブりとは？
始めに「COUNTIFS関数のOR条件で起きるダブりとは何か？」からです。
まずは、分かりやすい例を見てみます。
例えば先ほどの表をもとに「『男性』で『総務課』の人」か「『男性』で『東京都出身』の人」をカウントしてみます。
数式は、
=COUNTIFS(C4:C13,"男",D4:D13,"総務課")+COUNTIFS(C4:C13,"男",E4:E13,"東京都")
です。
すると、3人しかいないはずなのに、結果は「4」と返ってきました。
なぜでしょうか？
原因は「一人でどちらの条件にも当てはまった人がいる」ためです。つまり、ダブったのです。
№1の佐藤和夫さんが、「男で総務課」と「男で東京都出身」の両方に該当するため、どちらにもカウントされたのです。
このようなダブりは、OR条件の一つ目と二つ目で「検索対象範囲が違う時に起こる可能性があります。
２－２．カウントのダブりへの対処法
次に、カウントのダブりへの対処法です。
カウントのダブりなく正しい結果を得るためには、「『COUNTIFS関数同士を足したもの』から『どちらの条件にも当てはまる分を引く』」ことです。
引く分は、先ほどの例でいうと「『男性』で『総務課』で『東京都出身』の人数」です。
すると「男で総務課」は2人、「男で東京都出身」も2人いますが、「男で総務課で東京都出身」が1人いるので「2+2-1=3」と、正しく計算されます。
ちなみに例の数式は、
=COUNTIFS(C4:C13,"男",D4:D13,"総務課")+COUNTIFS(C4:C13,"男",E4:E13,"東京都")-COUNTIFS(C4:C13,"男",D4:D13,"総務課",E4:E13,"東京都")
です。
なお、「ダブりがあるかどうか分かりづらい」という場合には、「どんな場合でも全ての条件に当てはまる分を引いてみるとよい」ですよ。
【口コミ・評判のいいExcel本の紹介】
３．なぜ「カウントのダブり（重複分）」を引くのか？
一応最後に、なぜカウントのダブり（重複分）を引くのか、もう少し説明します。
今回のような条件指定では、色付けした表を見れば分かるようにように、「①の条件に当てはまるデータ」と、「②の条件に当てはまるデータ」、そして「①②両方に当てはまるデータ」が出てきます。
これらの関係を図にすると、次のようになります。
斜線部分は①と②が重なり合っているので、「①＋②だと斜線部分が二重に足されてしまいます」。
そのため重複分を差し引くのです。
言葉で説明すると難しいのですが、次のように覚えてください。
・①と②で指定した検索対象範囲が同じ場合には「単純に足す」
・①と②で指定した検索対象範囲が違う場合には「足した後重複分を引く」
以上、参考になれば幸いです。
COUNTIFS関数に関連したページは、次の通りです。。
- COUNTIFS関数 使いこなすためのポイントはこちら