いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)COUNTIF(S)関数で複数条件(AND,OR)を指定する方法

 COUNTIF(S)関数を使って値をカウントする際に複数の条件を指定する方法についてです。
 AND条件(かつ=すべての条件に該当)についてはCOUNTIFS関数を使えば問題ありませんが、OR条件(または=いずれかの条件に該当)の場合に少し工夫が必要です。

AND条件(かつ)の場合

 次の画像では、D3セルに次のような式を入力し、B列に並んでいる数値を「100以上」かつ「140以下」という複数条件でカウントしています。
 該当するものは100,120,105,140の4つなので結果は「4」となります(A列の赤字は条件に該当している値がわかりやすいように、手動で入力しているものです。以下同様)。

 D3セル

=COUNTIFS(B3:B9,">=100",B3:B9,"<=140")

 AND条件の場合はCOUNTIFS関数を使うことで簡単にカウントできます。
 このように同じ範囲に複数条件を付ける場合でも第1引数、第3引数に繰り返し同じ範囲を指定する必要があるのでご注意ください。
 なお、同様に範囲と条件の組を増やしていけば、3つ以上の条件をつけることもできます。



 次の画像は、同様にして複数の条件に該当するデータの件数(行数)を求める例です。
 データは事業者のリストになっており、このうち所在地が「東京都」で、かつ、従業員数が「1000以上」であるものの件数を求めています(セルの色はどの行がそれぞれの条件に該当しているかわかりやすいように、手動で入力したものです。以下同様)。

 F3セル

=COUNTIFS(C3:C9,"東京都",D3:D9,">=1000")

 やはりCOUNTIFS関数を使い、C列とD列のそれぞれの行に条件をつけています。
 これにより両方の条件を満たす行数が求められます。
 同様に範囲と条件の組を増やしていけば、3つ以上の条件をつけることができます。

OR条件(または)の場合

複数のCOUNTIF(S)関数の結果を足し算する方法

 最も手軽にOR条件のカウントができる方法です。ただし以降でみるように重複(二重)カウントには注意してください。


 次の画像では、D3セルに次のような式を入力し、B列に並んでいる数値を「100未満」または「140より大きい」という複数条件でカウントしています。
 該当するものは75,80,150の3つなので結果は「3」となります。

 F3セル

=COUNTIF(B3:B9,"<100")+COUNTIF(B3:B9,">140")

 とても単純で、2つのCOUNTIF関数の結果を足すだけで済みます。
 足し算を増やせば3つ以上の条件をつけることもできます。



 ただし両方(2つ以上)の条件に該当するデータがある場合は注意が必要です
 次の画像では、所在地が「東京都」または従業員数が「1000以上」である件数を求めようとしています。
 東京都であるものの数は4、1000以上であるものの数は5なので、単純に足し算すると結果は「9」となりますが、これは明らかに誤りです。

 F3セル(誤り)

=COUNTIF(C3:C9,"東京都")+COUNTIF(D3:D9,">=1000")

 この式だと両方の条件に該当する行を2回カウントしてしまい、それが誤りの原因です。



 そこで次のようにする必要があります。

 F3セル(正解)

=COUNTIF(C3:C9,"東京都")+COUNTIF(D3:D9,">=1000")
-COUNTIFS(C3:C9,"東京都",D3:D9,">=1000")

 改行(Atl+Enter)はなくてもOKです。
 両方の条件に該当するものを2回カウントしているのが誤りの原因なので、「-COUNTIFS(C3:C9,"東京都",D3:D9,">=1000")」により1回分(3)を差し引いています。
 これで正しい答えが求められます。
 ただし、この考え方では条件が3つ以上になると非常に複雑な計算が必要となってしまいます(ベン図を描いてみると何かわかるかもしれません)。その場合は以降で紹介する方法をおすすめします。

SUMPRODUCT関数を使う方法(旧バージョン用)

 スピル機能がない旧バージョン向けの方法です。もちろん新しいバージョンでも動作しますが、次のSUM/IFを使う方がわかりやすいです。ただしSUM/IFを使う方法はスピル機能がないバージョンでは使えないのでその点を踏まえて使用する方法をご判断ください。
 なお動作はExcel2010で確認していますが、画面はMicrosoft 365で実行したものです。


 次の画像では上記例と同様に、所在地が「東京都」または従業員数が「1000以上」の件数を求めています。
 結果は「6」となり、正しい結果になっていることがわかります。

 F3セル

=SUMPRODUCT(((C3:C9="東京都")+(D3:D9>=1000)>0)*1)

 SUMPRODUCT関数は本来「掛け算の合計(積和)」を求めるための関数ですが、複数の判定結果をカウントするのに都合がいいのでよく用いられます。
 しかし条件の記し方がCOUNTIF(S)関数の場合は異なっており、IF関数のような記し方となります。文字列との比較では「=」や「<>」が必要となり、数値との比較ではダブルクォーテーション("")を使いません。さらにそれぞれの条件をカッコで囲み足し算して「>0」をつけ、さらに全体をカッコで囲んで「*1」を付けるという厄介な式となっています。
 ただ、条件の数を増やして足し算するだけで簡単に3つ以上の条件をつけることができます。これが上記の方法と比べたときの大きなメリットです。



 参考までに、次の画像は「=(C3:C9="東京都")+(D3:D9>=1000)>0」の部分だけを実行した結果です(スピル機能のないバージョンではこのように表示されないので注意)。
 2つのカッコに記された条件判定結果はそれぞれTRUEまたはFALSEとなりますが、これらを足し算することで0~2の数値に変換されます(いくつの条件に該当しているかを意味します)。さらに「>0」で判定することで、1つ以上の条件に該当する行は「TRUE」に変換されます。

 あとは上記の式のように「*1」をつければTRUEが「1」に変換され、最終的にSUMPRODUCT関数で合計されます。SUM関数でもよさそうですがスピル機能がないバージョンではうまくいきません。

SUM/IF関数を使う方法(スピル機能のあるバージョン専用)

 スピル機能のある新しいバージョン向けの方法です。
 個々の関数は以前からあるものですがスピル機能のないバージョンでやると誤った結果になるので注意してください。 

 F3セル

=SUM(IF((C3:C9="東京都")+(D3:D9>=1000),1,0))

 条件の記し方はSUMPRODUCTの例に近いですがやや簡潔になります。
 IF関数で両方の条件を満たす行を「1」に、そうでない行を「0」に変換し、最後にSUM関数で合計(1を合計=カウント)するという内容です。この方法でも条件の数を増やして足し算するだけで3つ以上の条件をつけることができます
 うかつにスピル機能のないバージョンでやると誤った結果になる(最初の行しか対象にならず結果は0か1になる)のが痛いところですが、特に互換性に注意する必要がない場合はこちらの方がよいでしょう。