COUNTIF / COUNTIFS が動かない6つの原因と解決法【上から順に試せばOK】

「COUNTIF を書いたのに結果が 0 になる…」 「期待してた件数より少ない…」 「COUNTIFS で #VALUE! が出る…」

そんなとき、原因は だいたい決まったパターン にハマっています。

この記事は、

上から順に疑えば、原因が必ず1つに絞れる

という チェックリスト形式 で書いています。 読み終わるころには、自力で COUNTIF / COUNTIFS のトラブルを解決できるようになります。

💡 関連記事:

• SUMIF / SUMIFS が動かない6つの原因と解決法 ← 「合計」版・原因のパターンが似ている
• VLOOKUPで#N/Aが出る5つの原因と解決法 ← 検索系で困っている人向け
• Excelの主要エラー6種【症状→原因→解決の早見表】

まずはCOUNTIFとCOUNTIFSの基本（30秒で確認）

=COUNTIF(条件範囲, 条件)
=COUNTIFS(条件範囲1, 条件1, 条件範囲2, 条件2, ...)

COUNTIF と COUNTIFS は引数構造がシンプル ですが、注意点があります：

• COUNTIF は 条件範囲 → 条件 の2引数だけ
• COUNTIFS は 条件範囲と条件のペアを複数指定 できる
• どちらも 合計範囲は不要（数えるだけだから）

📌 ポイント: 条件1つなら COUNTIF、複数なら COUNTIFS。SUMIFと違って引数の順番に「合計範囲」が混ざらないので、ペアで素直に並べるだけです。複数条件ごとの件数・合計を一覧で見たい場合は、数式ではなく ピボットテーブルを使った集計 が手軽なこともあります。

まず疑うべき3つの原因（これで9割解決します）

COUNTIF / COUNTIFS が思い通りに動かない原因の 9割 はこの3つです。 上から順番に確認 してください。

① データ型の不一致（文字列⇔数値）

一番多い原因がこれです。SUMIFと同じパターン。

症状

• 検索条件（例："1001"）と、表の中の値（例：1001）が 見た目は同じなのに数えられない
• 結果が 0 になる
• 元データをCSVや別システムから貼り付けたときに発生しがち

確認方法

セルを選択して、画面下のステータスバーを見てください。

• 数値なら「合計」が表示される
• 文字列なら「合計」が出ない（「データの個数」だけ）

または、セルの中身が 左寄せ＝文字列、右寄せ＝数値 が初期動作です。

解決方法

条件範囲側を数値に変換するのが王道。一括で直すには：

1. 空のセルに 1 を入力してコピー
2. 数値化したい範囲を選択
3. 右クリック →「形式を選択して貼り付け」→「乗算」

これで文字列の数字が一気に数値になります。

📌 ポイント: 「条件範囲」と「条件として指定する値」の 両方が同じ型 になっている必要があります。片方が文字列、片方が数値だと一致しません。

② 見えない余白・改行が混じっている

「目視では同じなのに数えられない」場合、スペースや改行が混入している 可能性が高いです。

症状

• 条件が "商品A" なのに、表側が "商品A "（末尾に半角スペース）
• 別システムからのコピペデータでよく発生
• 結果が「期待より少ない」など中途半端な値になる

確認方法

セルをダブルクリックして、カーソルキーで末尾まで移動してみる。 余分な余白があると気づけます。

解決方法

TRIM関数 で範囲側をクリーンアップした作業列を作るのが鉄板です。

=TRIM(A2)

この作業列を作ってから COUNTIF の条件範囲に指定します。 直接 COUNTIF の中で TRIM することは できません。

📌 ポイント: TRIMは 半角スペース しか処理しません。全角スペース（　）が混じっている場合は、先に SUBSTITUTE で除去してください。

コピー

=TRIM(SUBSTITUTE(A2,"　",""))

③ COUNTIFS の範囲サイズが揃っていない

COUNTIFS には 致命的な仕様 があります。

すべての条件範囲は 同じサイズ（同じ行数・列数） でなければならない

症状

• #VALUE! エラーが出る

ダメな例

=COUNTIFS(A2:A100, "商品A", B2:B50, "東京")
                              ↑ ここだけ B2:B50 で範囲が短い

正しい例

=COUNTIFS(A2:A100, "商品A", B2:B100, "東京")

解決方法

すべての範囲を 同じ開始行・終了行 に揃えてください。 列全体を指定する方法も使えます：

=COUNTIFS(A:A, "商品A", B:B, "東京")

📌 ポイント: 列全体（A:A など）を使うと範囲ズレの心配が消えますが、データ量が多いと処理が重くなることがあります。実務では $A$2:$A$1000 のように 絶対参照＋十分な行数 を確保する書き方も定番。

それでも直らないときの中級チェック

御三家で原因が特定できないときは、以下を疑います。

④ 比較演算子のクォート忘れ

「80以上」や「東京以外」のような 条件式 は 文字列として クォートで囲む必要があります。

ダメな例

=COUNTIF(A2:A100, >=80)   ← >=80 がクォートされていない

正しい例

=COUNTIF(A2:A100, ">=80")
=COUNTIF(A2:A100, "<>東京")

セル参照を組み合わせるとき は、演算子だけクォートして & で連結します。

=COUNTIF(A2:A100, ">="&D1)

これは「D1 の値以上のもの件数」という意味。

📌 ポイント: 「= を含む条件」「<> を含む条件」は 必ずクォートが必要。クォートを忘れると #NAME? か想定外の結果になります。

⑤ ワイルドカード（* ?）と完全一致の混同

COUNTIF / COUNTIFS は デフォルトで完全一致 ですが、ワイルドカードも使えます。

ワイルドカードの意味

部分一致したいとき

=COUNTIF(A2:A100, "商品*")    ← "商品" で始まる
=COUNTIF(A2:A100, "*東京*")   ← "東京" を含む
=COUNTIF(A2:A100, "*店")      ← "店" で終わる

📌 ポイント: 部分一致で数えたいのに完全一致で書いている、または逆に完全一致のつもりが * を含む文字列が予想外にヒット…のパターンが多いです。

⑥ 空白セル・重複・大文字小文字の落とし穴

仕様を勘違いしやすい3つのケース。

空白セルを数える / 除外する

=COUNTBLANK(A2:A100)        ← 空白セルの個数
=COUNTIF(A2:A100, "<>")     ← 何か入力があるセルの個数
=COUNTA(A2:A100)            ← 空白でないセルの個数（文字列・数値問わず）

COUNTIF(A2:A100,"") は「空文字」を数えるので、完全な空セル と 数式が返す "" で挙動が変わることがあります。

重複は どちらもカウントされる

=COUNTIF(A:A, "商品A") は「商品A」が10行あれば10と返ります。ユニーク数を数えたい ときは別の式を使います：

=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))

⚠️ この式は範囲内に空白セルがあると #DIV/0! エラーになります（空白を COUNTIF が 0 と数え、1/0 が発生するため）。空白が混じる場合は下の UNIQUE 版が安全です。

または Microsoft 365 / Excel 2021 以降なら：

=COUNTA(UNIQUE(A2:A100))

大文字小文字は 区別されない

"abc" と "ABC" は同じ扱い。区別したい場合は SUMPRODUCT + EXACT を使います。

=SUMPRODUCT(--EXACT(A2:A100, "ABC"))

まとめ：判断フロー

この順番で確認すれば、ほぼ全ての COUNTIF / COUNTIFS のトラブルが解決できます。

よくある質問

Q. COUNTIFS と COUNTIF、どちらを覚えるべき？

A. COUNTIFS を覚えれば十分 です。条件1つでも COUNTIFS で書けるので、統一感が出てメンテナンスしやすくなります。

Q. 「○○以外」をカウントしたい

A. =COUNTIF(A:A, "<>東京") のように <> を使います。空白以外なら "<>"。

Q. 結果がどうしても合わない…

記事の手順を試しても直らない場合、特定の構成だけで起きるレアケースかもしれません。AIサポートで対話的に原因を切り分けてみてください。