Excel約5分で読めます

ExcelのCOUNTIFが0になる|条件の書き方とワイルドカード

ExcelのCOUNTIFが0になるを表したイメージ
記事の内容を表したイメージ(AI生成)

COUNTIFの結果が0になるとき、セルに値は確かに入っているのに数えてもらえない、という状況がほとんどです。原因の大半は「条件の書き方」にあります。数式の構造が壊れているのではなく、条件として渡している文字列の形が、セルの中身と微妙にずれています。

この記事では、COUNTIFが0を返す原因を「完全一致のずれ」と「ワイルドカードの使い方の誤り」に絞って説明します。関数の基本的な引数の順番よりも、「なぜ一致しないのか」の判断と、その直し方を中心に扱います。

作業の前に、必ずファイルをコピーして元を残しておいてください。数式を書き直しても元の状態に戻せるよう、編集はコピーしたファイルで行うのが安全です。


完全一致でも0になる三つの原因

COUNTIFは条件と完全に一致するセルだけを数えます。見た目が同じでも、次の三つのどれかがずれていると0になります。

1. 前後にスペースが混入している

セルに「東京都 」(末尾に半角スペース)と入っていても、条件に「東京都」と書くと一致しません。反対に、条件側にスペースが紛れ込んでいる場合も同じです。確認するには、該当セルを選択してから数式バーをよく見るか、=LEN(A2) で文字数を調べます。「東京都」なら3文字のはずが4文字になっていれば、スペースが含まれています。

2. 全角・半角が混在している

「ABC」(半角)と「ABC」(全角)はCOUNTIFには別の文字列です。入力者が複数いる名簿や、システムから書き出したデータに混在しがちです。セルを選択して数式バーで一文字ずつ確認するか、=EXACT(A2,"ABC") で大文字・小文字・全半角をすべて区別した比較ができます(TRUEなら一致)。

3. 数値と文字列の型が違う

セルに数字の「001」が文字列として入力されている場合、条件に数値の 1 を渡しても一致しません。逆もあります。セルの左上に緑の三角が出ていれば、数字が文字列として保存されているサインです(画面の表示は版や設定により異なります)。条件を "001" のようにダブルクォーテーションで囲んで文字列として渡すか、データ側を統一する必要があります。


ワイルドカードが効かない二つのケース

COUNTIFでは *(任意の文字列)と ?(任意の一文字)をワイルドカードとして使えます。ただし、効かない場面が二つあります。

数値セルにはワイルドカードが使えない

ワイルドカードは文字列の条件にしか機能しません。セルの値が数値(たとえば 1001)の場合、"100*" という条件を渡しても0が返ります。数値を部分的に一致させたい場合は、TEXT 関数でいったん文字列に変換した列を別に用意してから数えるか、SUMPRODUCT と ISNUMBER を組み合わせる方法を検討してください。

*** 自体をそのまま探したいとき**

アスタリスク * を文字として含むセルを数えたい場合、"*" と書くと「すべての文字列」を指定したことになり、意図しない結果になります。文字としての * を検索するには、チルダ ~ を前に付けて "~*" と書きます。? も同様に "~?" とします。


条件の書き方を確認する手順

以下は架空の例です。「担当者」列(A列)に次のデータが入っているとします。

表は横にスクロールできます

A列(担当者)
鈴木 一郎(末尾スペースあり)
鈴木一郎
鈴木一郎

=COUNTIF(A1:A3,"鈴木一郎") の結果が 2 になる場合、A1のセルにスペースが混在しています。3つ数えたいなら、データ側を TRIM 関数でスペースを除去して整えるか、条件にワイルドカードを使って "*鈴木一郎*" とする方法があります。ただし後者は「鈴木一郎」を含む別の文字列(例:「副鈴木一郎」)も数えてしまうため、データをきれいにする方が長期的には安全です。

迷いやすい例外:条件をセル参照で渡す場合

条件を直接書かず、別のセルに入力した値をC1のように参照することもあります。このとき、C1の値に不要なスペースや改行が入っていると、やはり0になります。参照先のセルも LEN や EXACT で確認してください。また、比較演算子(">=" など)と組み合わせる場合は ">="&C1 のように & でつなぐのが正しい書き方です。">=C1" とそのまま書いてしまうと、C1がセル参照として解釈されず文字列の「C1」と比較されて0になります。


それでも0になるときのチェックリスト

上記を確認しても解決しない場合、次の順番で見直します。

  1. 範囲と条件を最小にして試す — 1セルだけを範囲にして =COUNTIF(A1,"東京都") のように書き、A1の値と条件が完全に一致するか確かめます。
  2. 条件を数式バーから直接コピーする — 別のセルに入力した値を条件に使う場合、キーボードで打ち直すのではなく、そのセルの数式バーから値をコピーして条件欄に貼ります。コピーペーストでスペースごと取り込んでしまったことに気づける場合があります。
  3. COUNTAで総数を確認する — 範囲内のセルが実際にいくつあるか =COUNTA(A1:A10) で確認します。思ったより少ない場合、範囲の指定が意図と違っている可能性があります。
  4. フィルターで目視確認する — 列にフィルターをかけて、条件にしたい値だけを絞り込んで表示します。そのとき表示件数と一致する数値をCOUNTIFが返さない場合、型や見えない文字の問題がほぼ確実です。
  5. 数式を別シートに移して試す — 稀に、シートに壊れた条件付き書式や循環参照があり、計算結果に影響している場合があります。新しいシートにデータと数式だけ貼り付けて動作を確認すると切り分けに使えます。

根本的に直すか、その場で回避するか

スペースや全角・半角の混在はデータ整備で解決するのが本来の対処です。ただし、今日の締切に間に合わせなければならない状況では、ワイルドカードや TRIM を組み合わせて一時的に数式で吸収するのも現実的な選択です。どちらを選ぶかは、そのデータが今後も更新され続けるかどうかで判断してください。毎週追記される名簿であれば、データの入力ルールを整えた方が後の手間が減ります。一度きりの集計であれば、数式で回避する方が早い場合があります。


次の行動

まず =LEN(確認したいセル) で文字数を調べ、想定より多ければスペースや見えない文字の混入を疑ってください。次に =EXACT(A2,条件) でTRUE/FALSEを確認し、型と文字が完全に一致しているかを確かめます。ワイルドカードを使うときは、対象が文字列かどうかを先に確認し、数値セルには使えないことを念頭に置いてください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ