「担当者ごとの件数を数えただけなのに、全体の合計と数が合わない」
COUNTIF関数を使っていて、そんな違和感を覚えたことはないでしょうか。関数自体は正しく書けているはずなのに、手元の実際のデータと突き合わせると、なぜか数がずれている。原因を探っていくと、たいてい「見た目は同じに見えるけれど、Excelにとっては別物」というデータの表記ゆれが隠れています。
COUNTIF関数は、条件に一致するセルの個数を数える関数です。SUM関数と並んで実務での使用頻度が高く、集計表やチェックリストなど、あらゆる場面で登場します。今回は基本の書き方から、実務でよくある「数えたはずなのに数が合わない」というトラブルの原因まで、一通り整理していきます。
1. COUNTIF関数とは
COUNTIF関数は、指定した範囲の中で、条件に一致するセルの個数を数える関数です。書式は次の通りです。
=COUNTIF(範囲, 検索条件)- 範囲：数えたいセル範囲を指定します。
- 検索条件：数える対象を絞り込む条件を指定します。
たとえば、A1からA20の範囲に入っている「完了」という文字列の件数を数えたい場合、次のように書きます。
=COUNTIF(A1:A20, "完了")条件に文字列を指定する場合はダブルクォーテーションで囲みます。数値をそのまま条件にする場合は、クォーテーションは不要です。
2. 実務でよくある使用例
2-1. アンケートの回答数を選択肢ごとに集計する
=COUNTIF(B2:B100, "満足")自由記述ではなく選択式のアンケートで、各選択肢が何件あったかを数えるときの定番の使い方です。選択肢の数だけCOUNTIF関数を並べれば、簡単な集計表がすぐに作れます。
2-2. 重複しているデータの件数を確認する
=COUNTIF(C2:C500, C2)C2のセルと同じ値がC列全体に何件あるかを数える例です。結果が2以上になっているセルがあれば、そのデータは重複している可能性があります。すべての行に同じ数式をコピーして、結果が1より大きい行だけをフィルターで絞り込むと、重複データの洗い出しに使えます。
2-3. 比較演算子を使って条件を数値で指定する
=COUNTIF(D2:D50, ">=80")80点以上のセルの件数を数える例です。比較演算子を使う場合は、演算子と数値をまとめてダブルクォーテーションで囲む、という書き方になります。「80点以上は何人？」のような集計でよく使われます。
2-4. ワイルドカードで部分一致を数える
=COUNTIF(E2:E200, "*東京*")アスタリスク（*）はワイルドカードと呼ばれ、任意の文字列を表します。「東京」という文字を含むセルをすべて数えたい場合に使います。「東京都渋谷区」「東京都新宿区」のように、住所の一部が違っていても「東京」を含むセルをまとめて数えられます。
2-5. 未入力の行がどれだけ残っているか確認する
=COUNTIF(F2:F100, "")進捗管理表で「まだ入力されていない行」がどれだけ残っているかを確認したいときの使い方です。空白セルの件数がゼロになれば、全件入力が完了したことがひと目でわかります。日々の進捗チェックに組み込んでおくと、入力漏れに早く気づけます。
2-6. 前方一致・後方一致で条件を絞り込む
=COUNTIF(G2:G100, "東京*")ワイルドカードは、文字列のどの位置に使うかで意味が変わります。「東京*」のように後ろに付ければ「東京」で始まるセルを数える前方一致になり、「*東京」のように前に付ければ「東京」で終わるセルを数える後方一致になります。「東京都から始まる住所だけを数えたい」「〜株式会社で終わる会社名だけを数えたい」といった場面で使い分けます。
2-7. 条件付き書式と組み合わせて重複を目立たせる
COUNTIF関数は、条件付き書式のルールとしてもよく使われます。「セルの値を含む数式」として =COUNTIF($C$2:$C$500, C2)>1 のような数式を条件付き書式に設定しておくと、重複しているセルに自動で色がつくようになります。2-2で紹介した重複チェックの数式を、別のセルに表示するのではなく、表そのものの上で視覚的に確認したいときに便利な応用です。
3. COUNTA関数・COUNT関数との違い
COUNTIF関数と混同しやすい関数に、COUNT関数とCOUNTA関数があります。役割の違いを整理しておきます。
- COUNT関数：範囲内の「数値が入っているセル」の個数を数えます。条件は指定できません。
- COUNTA関数：範囲内の「何かしらのデータが入っているセル」の個数を数えます。文字列・数値どちらもカウント対象になります。条件は指定できません。
- COUNTIF関数：範囲内の「指定した条件に一致するセル」の個数を数えます。
「回答済みの行が何件あるか」を数えたいだけであれば、条件を指定できるCOUNTIF関数よりも、シンプルにCOUNTA関数を使ったほうが数式がすっきりします。逆に「特定の選択肢を選んだ件数」のように、内容を絞り込んで数えたい場合はCOUNTIF関数が向いています。目的に応じてこの3つを使い分けられるようになると、無駄に複雑な数式を書かずに済むようになります。似た名前の関数が並んでいると、つい一番よく使うCOUNTIFだけで何でも解決しようとしてしまいがちですが、シンプルな集計にはシンプルな関数を選ぶほうが、後から数式を見直す人にとっても親切です。
4. COUNTIF関数でよくある間違い
4-1. 全角と半角の違いに気づかない
見た目は同じ「1」でも、全角の「１」と半角の「1」はCOUNTIF関数にとって別の文字として扱われます。他部署から共有されたデータや、Webからコピーしてきたデータでは、全角と半角が混在していることがよくあり、これが「数えたはずなのに数が合わない」という違和感の代表的な原因になります。
4-2. セルの前後に見えないスペースが入っている
「東京」と入力したつもりのセルに、末尾に半角スペースが1つ紛れ込んでいるだけで、COUNTIF関数は完全一致とは判定してくれません。見た目では気づきにくいこの手のスペースは、実務でのデータ集計トラブルの中でもかなり頻度の高い原因です。心当たりがある場合は、対象範囲にTRIM関数をかけて余分なスペースを取り除いてから数え直すと、正しい件数が確認できます。
4-3. 数値と文字列が混在していることに気づかない
見た目は同じ数字でも、セルによって数値として入力されているものと、文字列として入力されているものが混在していることがあります。COUNTIF関数で数値条件を指定した場合、文字列として入力されているセルはカウント対象から外れてしまうため、想定より少ない件数が返ってくる原因になります。
4-4. 条件の書き方を間違える（等号を書いてしまう）
「80点に一致するセルを数えたい」というつもりで「=”80″」のように等号を含めて書いてしまうと、正しく動作しないことがあります。単純な完全一致であれば、等号なしでそのまま値を指定すれば十分です。等号が必要になるのは、セルが完全に空白かどうかを判定する「=””」のような特殊なケースに限られます。
4-5. 複数条件を1つのCOUNTIFに無理に詰め込もうとする
COUNTIF関数は、条件を1つしか指定できません。「東京都かつ満足」のように条件が2つ以上になる場合は、COUNTIF関数を工夫して組み合わせるのではなく、次の章で紹介するCOUNTIFS関数に切り替えるのが正しい対処法です。
4-6. 参照範囲を列全体（A:A）にして、意図しないセルまで拾ってしまう
「後からデータが増えても対応できるように」と、範囲をA2:A1000のように広めに取ったり、A:Aのように列全体を指定したりすることがあります。便利な一方で、表の下の方に別の集計用の数式やメモが紛れ込んでいると、そのセルまで条件に一致してカウントされてしまうことがあります。範囲を広く取る場合は、集計対象の表と、それ以外のメモや数式を別のシートに分けておくと、この手の事故を防ぎやすくなります。また、テーブル機能を使って範囲を自動拡張させておく方法も有効で、データが増えたときに数式の範囲を手動で書き換える手間そのものをなくすことができます。
5. 条件が2つ以上になったら：COUNTIFS関数へ
COUNTIF関数は条件を1つしか扱えないため、複数の条件を同時に満たすセルを数えたい場合は、COUNTIFS関数を使います。書き方はCOUNTIFとよく似ていますが、「範囲と条件のペア」を複数並べて指定できる点が異なります。ペアの数が増えるほど、範囲同士の行数がずれていないかの確認が重要になり、これがCOUNTIFSならではのつまずきポイントにもなります。COUNTIFSの詳しい使い方や、条件範囲がズレてしまう典型的なミスについては、別の記事で詳しく取り上げていますので、複数条件で数えたい方はあわせて確認してみてください。
COUNTIFS関数の使い方をわかりやすく解説｜複数条件で数が合わないときの確認ポイント https://www.excel-laboratory.online/countifs-kansu/
よくある質問
Q. COUNTIF関数で空白セルの件数を数えるにはどうすればいいですか？
=COUNTIF(範囲, "") と書くことで、完全に空白のセルの件数を数えられます。ただし、見た目は空白でも、実は数式の結果として空の文字列が入っているセルは、この条件でカウントされる場合とされない場合があるため、心配な場合はCOUNTBLANK関数もあわせて確認することをおすすめします。
Q. COUNTIFの検索条件に、別のセルの値を使うことはできますか？
できます。=COUNTIF(範囲, F1) のようにセル参照を条件にすると、F1セルの値を変えるだけで集計結果が自動的に切り替わる、柔軟な集計表を作れます。条件をいちいち数式の中に書き直す必要がなくなるため、頻繁に条件を変えて確認したい場面では、セル参照を使う方法をおすすめします。
Q. 大文字と小文字は区別されますか？
COUNTIF関数は、基本的にアルファベットの大文字・小文字を区別せずに一致を判定します。「Tokyo」と「tokyo」は同じものとしてカウントされる点は、他の多くのExcel関数と共通する挙動です。
Q. 「以上」と「より大きい」の書き方を間違えやすいのですが、どう覚えればいいですか？
「>=」は「以上」（その数値を含む）、「>」は「より大きい」（その数値を含まない）と覚えておくと整理しやすいです。境界値をどちらに含めるかは業務のルール次第なので、条件を書く前に「ちょうどその数値の場合はカウントに含めるかどうか」を確認しておくと、後から数え直す手間を防げます。
まとめ
COUNTIF関数は書き方自体はシンプルですが、「数えたはずなのに数が合わない」というトラブルの多くは、関数の書き方ではなく元データの表記ゆれに原因があります。
- 全角・半角、見えないスペースの混入を疑う
- 数値と文字列が混在していないか確認する
- 完全一致か部分一致（ワイルドカード）かを意識して条件を書く
- 条件が2つ以上になったらCOUNTIFS関数に切り替える
数える対象のデータそのものを一度きれいに整えておくだけで、COUNTIF関数まわりの「なぜか合わない」はかなり減らせます。
もし「数式は合っているはずなのに結果がおかしい」という状況に出会ったら、まずCOUNTIF関数そのものを疑う前に、数える対象のセルを実際にクリックして、中身が本当に想定通りの値になっているかを目で確認してみてください。関数のミスよりも、元データの表記ゆれが原因になっているケースのほうが、実務では圧倒的に多いというのが実感です。焦らず、一つずつ確認していきましょう。
コメント