Excel 作業の効率が上がる! 条件に合うセルを数える COUNTIF 関数を徹底解説

2022 年 1 月 19 日

Microsoft Excel 上で特定の条件に適合するセルを数えるときに便利な COUNTIF 関数。
一度使い方を覚えてしまえば、多くの場面で役に立ちます。
今回は、COUNTIF 関数の基本的な使い方から、集計レベルを上げる応用的な方法まで解説します。

Excel を使って日々業務をしていると、「この条件に合致する対象は何名いるか?」「条件に合わない会社は何社あるのか?」など、特定条件に合致するセルを集計したいときがあります。COUNTIF 関数は、そのような場面に適した関数で、さまざまなニーズに対応可能です。

ここではまず、COUNTIF 関数の役割や基本的な使い方について見ていきましょう。

COUNTIF は指定した条件に合うセルの個数をカウントしたいときに使う関数です。データの個数を数える「COUNT」関数と条件を指定して結果を返す「IF」関数の両方の機能が合体した機能で、大量のデータを集計する際に役立ちます。

具体的には、「試験で 80 点以上取った人の数を数えたい」「表の中に ”東京” という文字が入ったセルを数えたい」「日付が "2021 年 10 月" のセルを数えたい」など、条件に合ったデータが入っているセルの数を COUNTIF は簡単に求めてくれるのです。

このように、手計算でやると手間や時間がかかる作業でも、COUNTIF を使えば大量のデータを短時間で処理できます。

次に、COUNTIF 関数の標準的な使い方を解説します。

【関数式】　=COUNTIF(検索範囲,検索条件)
検索範囲：検索したい条件が含まれているリストの範囲
検索条件：検索したい条件

それでは以下のような表で、COUNTIF を使ってみましょう。

渋谷カンパニーでは 2021 年度の売上一覧をまとめており、「顧客名が ”ABC商会” からの売上が何回あったか」を知りたいとします。

検索範囲：顧客名が入っている C3 セルから C9 セルを調べる
検索条件：「ABC商会」であること (文字列の場合は「”」でくくって表記する必要あり)

上記の検索範囲と検索条件から、「=COUNTIF(C3:C9,”ABC商会”)」と式を入れることで、ABC商会からの売上回数は 2 回であることがわかりました。

検索条件としては文字列の他にも、数値、日付などが指定可能で、「売上額が 25 万円である売上の数」であれば、「=COUNTIF(D3:D9,250000)」のように、「売上の日付が 2021 年 7 月 10 日である売上の数」であれば、「=COUNTIF(B3:B9,"2021/7/10")」のように指定することで条件に合うセルの数をカウントできます。

しかし、このように検索条件に直接値を入力すると、他の条件で検索したいときに毎回式を書き換えなくてはいけません。
その場合、検索条件に条件を入力するセル名を指定することで、条件を入力するセルの中身を変更すれば自動的に値を計算しなおしてくれるので便利です。

上記のように条件は「G2」のセルに入力するようにし、COUNTIF 式の検索条件には「G2」と入力しておくと、G2 セルに入力された値を条件にした計算結果が表示されます。

そうすると、G2 セルの値を変更すれば、式を変更しなくても結果が変わることが確認できます。

次に、もう少し COUNTIF 関数の理解を深めたい方向けに、COUNTIF 関数の応用を解説します。

COUNTIF 関数を使えば、以下のようなケースにも対応できます。

「○○以上」や「○日よりも後」というように、数値や日付を、比較演算子を使って指定したい場合もあるでしょう。
そのような場合には、不等号と等号を組み合わせて条件を指定できます。

たとえば、「売上額が 50 万円以上だった回数」を知りたい場合は、「=COUNTIF(D3:D9,">=500000")」と指定します。
このように、比較演算子を使う場合は条件式をダブル クォーテーションで囲む必要があります。

検索範囲となるデータが今後も増えていく可能性がある場合、データが増える度に式を編集しなくてはなりません。そんなときは、検索範囲として行や列の全体を指定することができます。

このようにデータが行方向に追加される可能性がある場合は、検索条件として D 列全体を「D:D」の形で指定することにより、データの追加があっても式の変更をせずに対応できます。

表が複数に分かれており、検索範囲が複数の行や列に渡っている場合はどうすればよいでしょうか。

上記の表のように複数の列から条件に合うセルをカウントしたい場合、検索範囲のセルが連続していれば COUNTIF を利用することが可能です。検索範囲となるセルは、複数の行列にまたがって指定することもできます。

ただし、これは他の列に本来はカウントすべきではない、似たようなデータが入っていないときのみ使える方法です。

検索範囲がシートをまたいでいたり、検索範囲の列の間にカウントすべきではないデータが入っていたりする場合は、上記の図のように COUNTIF を使って 1 つの検索範囲ごとにそれぞれ個別にカウントし、カウント後に合算する必要があります。

もしくは、事前に 1 列ないしは 1 行に収まるよう、表を一本化してからカウントするとよいでしょう。

検索を行うときには、「〜から始まる」「〜を含む」などのあいまい検索をしたいケースも多いでしょう。そういったときにはワイルド カードとして「*」 (アスタリスク) や「?」 (クエスチョン マーク) の文字を使うことができます。

「*」 (アスタリスク) は任意の文字数の値を代替するものです。「ABC」から始まる顧客から売上があがった数を知りたいときは、「ABC」の後の文字数は任意ですので、”ABC*” と入力することで「ABC商会」も「ABCエンタープライズ」も検索対象になります。

「?」は 1 文字の値を代替するものです。「ABC」から始まる「5 文字の」顧客から売上があがった数を知りたいときは、「ABC」の後の文字数は 2 文字となるので、”ABC??” と入力すれば、文字数も指定することができます。

今までは同じシートの中を検索範囲としていましたが、参照先が別シートや別ブックでも問題ありません。

たとえば、シート「取引履歴」に、2020 年度の取引履歴があったどうかの情報が入っているとします。

別シートに COUNTIF 関数を入力して検索範囲を指定する際は、「シート名!範囲」となります。
実際に「=COUNTIF(」まで入力し、別シートを開いて検索範囲をドラッグ & ドロップすると、自動的に入力されます。この場合は「取引履歴!C3:C7」です。

別ブックの場合には「[ブック名]シート名!セル範囲」となります。ブック名は[]で囲みます。もしこの取引履歴のシートが「2020 度実績」という名前のブックにある場合は、「=COUNTIF([2020年度実績]取引履歴!C3:C7, "○")」と記述することになります。

便利な COUNTIF 関数ですが、残念ながら検索条件が 2 つ以上になる場合には、式が複雑になってしまいます。
そこで、そんな場合に便利な「COUNTIFS」という関数を紹介します。基本的な使い方は以下のとおりです。

【関数式】　=COUNTIFS(検索範囲1,検索条件1, 検索範囲2,検索条件2…)

COUNTIF のように COUNTIFS 関数では、検索範囲と検索条件をカンマ区切りで並べていけば、簡単に複数の条件を扱うことができます。以下に、COUNTIFS が便利に使えるケースを紹介します。

上記の表では「東京都にあり、売上 500 万円以上の顧客の数」を求めています。
「=COUNTIFS(C4:C10,"東京都", D4:D10,">=5000000")」の形で、C 列と D 列を両方検索範囲として満たすものをカウントしています。

「〜以上〜未満」といった、数値的に 2 つ以上の条件が必要なものにも有用です。上記の表では、「売り上げが 300 万円以上 500 万円未満の顧客の数」を、「=COUNTIFS(D4:D10,"<5000000", D4:D10,">=3000000")」という形で求めています。

COUNTIFS も COUNTIF 同様にワイルド カードを使うことができます。上記の表では「社名が『ABC』で始まり売上が 500 万円以上の顧客の数」を、「=COUNTIFS(B4:B10,"ABC*",D4:D10,">=5000000")」という形で求めています。

なお、COUNTIFS 関数が使えるのは Excel 2007 以降となっています。
それより古いバージョンの Excelでは使えませんので注意してください。

ここでは、COUNTIF を使おうとしたけれどうまくいかない、という場合に役立つ情報を紹介します。 

COUNTIF を使ったときにエラーが出たり、思った結果が出なかったりする場合は、以下のような間違いが考えられます。

COUNTIF を使う場合、検索範囲と検索条件の記述を逆にしてエラーになってしまうケースをよく見かけます。

この場合は式を入力した時点でエラーのポップアップが出てくるため間違いに気付くことも多いと思いますが、必ず引数にはセルの検索範囲が最初にくることを意識しておきましょう。

検索範囲の指定を間違えて、「顧客名」から検索したいのに「売上額」のセルを検索範囲に指定してしまったり、または「顧客名」の途中までしか指定できていなかったりするケースです。特に検索範囲の不足は、COUNTIF 関数を入力した後でデータが更新された場合にたびたび起こります。
前述の通り、新しいデータが後日追加されるような場合は列や行全体を検索範囲に指定しましょう。

上の表では本来は「ABC商会」という顧客名を検索したいのに、検索範囲として売上額の列を指定しているので、値は 0 になってしまいます。

上の表では、「ABC商会」という顧客名を検索するための検索範囲として、C3 から C4 の 2 行しか指定されていません。そのため、本来値は 2 ですが、1 つしかカウントされていません。

ごくまれにですが、数字が文字列として入力されていることで思った結果が出ない場合があります。これは、元のデータが文字列になっていたところをコピーしたときに起こるものです。

上記の場合、「売上額」に文字列として扱われている数字があるために、正しく判定できていません。現在の Excel では、このような場合は注意喚起のアイコンが表示されることが多いですが、注意が必要です。

文字列、日付、式をダブル クォーテーション (””) で囲んでいないと、正しくカウントされません。

上記のケースですと、文字列なのにダブル クォーテーションで囲んでいないため、値が 0 になってしまっています。

逆に、数値やセルを指定する場合はダブル クォーテーションは不要です。

そのほか、検索条件にセルを指定しているのに、ダブル クォーテーションで囲んでしまって正しい結果が出ないといったケースも見受けられますので注意しましょう。

COUNTIF 関数は条件に合うデータが入ったセルの個数を数える関数で、集計の省力化、時間短縮に貢献してくれる便利なものです。ただし、条件が複数になった場合には使えないので、「COUNTIFS」を使用することを推奨します。

COUNTIF 関数が使える場合と使えない場合をよく理解し、さまざまな条件を指定することで、皆さんの仕事の強い味方となってくれることでしょう。