COUNTIFで非表示セルをカウントしない方法｜SUBTOTAL関数で可視セルだけ集計【完全ガイド】

Excelでフィルターをかけた後にデータをカウントしようとして、「あれ？非表示にしたセルまで数えられてる…」と困った経験はありませんか？Excel資料作成で頼られている人ほどハマる現象ですね。

結論から言うと、COUNTIF関数は非表示セルもカウントしてしまいます。可視セル（表示されているセル）だけを数えたい場合は、SUBTOTAL関数（集計方法109）やSUMPRODUCT関数、Microsoft 365ならFILTER関数を組み合わせるのが正解です。

本記事では、COUNTIFとSUBTOTALの違いから、フィルター後に条件付きでカウントする方法まで、コピペで使える数式付きで徹底解説します。

✔ 本記事のポイント

COUNTIF関数は非表示セルもカウントしてしまう（仕様）
SUBTOTAL関数(109)なら可視セルだけカウントできる
SUBTOTAL集計方法「9」と「109」の違いを完全解説
フィルター後に条件付きでカウントする方法（SUMPRODUCT・FILTER関数）
作業列を活用して誰でも理解できる数式に

【最短で解決】非表示セルを除外してカウントする数式（コピペOK）
COUNTIF関数の基本｜条件に一致するデータをカウント
1. COUNTIF関数の構文
2. COUNTIFの使用例
COUNTIF関数の問題点｜なぜ非表示セルもカウントされるのか
SUBTOTAL関数で非表示セルをカウントしない方法
【応用】フィルター後に条件付きでカウントする方法
SUBTOTAL関数とAGGREGATE関数の違い
効率的なデータ集計術｜作業列を活用するべし！
1. 作業列の設定方法
2. 作業列を活用するメリット
よくある質問（FAQ）
まとめ｜COUNTIFとSUBTOTALの使い分けをマスターしよう
1. 📌 この記事の重要ポイント

【最短で解決】非表示セルを除外してカウントする数式（コピペOK）

とにかくすぐに使いたい方向けに、結論の数式を3つ紹介します。

① 単純に可視セルの数を数える（条件なし）

=SUBTOTAL(103, A2:A100)

② フィルター後の特定条件をカウント（例：「りんご」の数）

=SUMPRODUCT((A2:A100="りんご")*(SUBTOTAL(103,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))))

③ Microsoft 365 / Excel 2021以降ならFILTER関数で簡単に

=COUNTA(FILTER(A2:A100,A2:A100="りんご"))

※ FILTER関数はフィルターで非表示にしたセルは無視できないため、フィルター連動が必要なら②のSUMPRODUCT方式がベストです。

COUNTIF関数の基本｜条件に一致するデータをカウント

ExcelのCOUNTIF関数は、指定した条件に一致するセルの個数を数える関数です。

COUNTIF関数の構文

=COUNTIF(範囲, 条件)

COUNTIFの使用例

例えば、次のようなデータがあるとします。

行	A列
2	りんご
3	みかん
4	りんご　（非表示行）
5	ドラゴンフルーツ
6	ドリアン

「りんご」の数をカウントするには、以下の数式を入力します。

=COUNTIF(A2:A6, "りんご")

結果は 2 になります。

ここで問題が発生！可視セルだけで集計したいのに、COUNTIF関数は非表示のデータも数えてしまいます。本来なら結果を1にしたいのに、思い通りにいきません。

COUNTIF関数の問題点｜なぜ非表示セルもカウントされるのか

COUNTIF関数の最大の弱点は、フィルターや手動で非表示にしたデータもカウントしてしまうことです。

これは「バグ」ではなくExcelの仕様です。COUNTIF関数は「指定範囲内で条件に一致するセルを機械的に数える」設計のため、表示状態（可視/非表示）を判別する機能を持っていません。

例えば、フィルターで「りんご」を非表示にして「みかん」「ドリアン」だけを表示しても、COUNTIF関数は裏で非表示になっている「りんご」も数え続けます。

「非表示データをカウントから除外したい！」――そんなときに活躍するのが SUBTOTAL関数 です。

SUBTOTAL関数で非表示セルをカウントしない方法

SUBTOTAL関数を使えば、フィルターで非表示にしたデータをカウントから除外できます。

SUBTOTAL関数の構文

=SUBTOTAL(集計方法, 範囲)

カウント用の集計方法は「2」「3」「102」「103」

集計方法	動作	フィルターで非表示	手動で非表示（行）
2	COUNT（数値のみ）	除外	含める
3	COUNTA（空白以外）	除外	含める
102	COUNT（数値のみ）	除外	除外
103	COUNTA（空白以外）	除外	除外

💡 ポイント：「3」と「103」の違いは何？

3：オートフィルターで非表示になったセルは除外。ただし、行番号を右クリックして「非表示」にしたセルは含めてカウントします。
103：オートフィルターでも手動非表示でも、両方とも除外してカウントします。

迷ったら「103」を使えば、どちらの非表示にも対応できて安全です。

SUBTOTAL関数の使用例

A列（商品名）	B列（作業列）
りんご	1
みかん	1
りんご	1
ドラゴンフルーツ	1
ドリアン	1

🔹 B2:B6のデータを可視セルだけカウントする場合

=SUBTOTAL(103, B2:B6)

✅ オートフィルターや手動で「りんご」を非表示にすると、カウントから除外されます。

【応用】フィルター後に条件付きでカウントする方法

「フィルターで絞り込んだ状態で、さらに『りんごの個数だけ』を数えたい」――この場合、SUBTOTALだけでは対応できません。条件付きカウントが必要です。

方法①：SUMPRODUCT × SUBTOTAL × OFFSET（全バージョン対応）

=SUMPRODUCT((A2:A100="りんご")*(SUBTOTAL(103,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))))

少し複雑に見えますが、仕組みはこうです。

A2:A100="りんご" → 条件に一致するセルを判定
SUBTOTAL(103,OFFSET(...)) → 各セルが表示されているか判定（1=表示、0=非表示）
SUMPRODUCTで両方を掛け合わせてカウント

これで「フィルター後に表示されている『りんご』の数」だけを集計できます。

方法②：FILTER × COUNTA（Microsoft 365 / Excel 2021以降）

=COUNTA(FILTER(A2:A100,A2:A100="りんご"))

FILTER関数で「りんご」だけを抽出し、COUNTAで数を数えます。

ただし注意点として、FILTER関数はオートフィルターによる非表示は無視できません。フィルター連動が必要な場合は、方法①のSUMPRODUCT方式を使ってください。

方法③：作業列＋COUNTIFS（最も分かりやすい）

SUBTOTALの結果を作業列に出してから、COUNTIFSで条件を絞る方法です。

B列（作業列）に以下を入力：

=SUBTOTAL(103,A2)

カウント数式：

=COUNTIFS(A2:A100,"りんご",B2:B100,1)

数式の意味が直感的に分かるため、共有ファイルではこの方式が最もおすすめです。

SUBTOTAL関数とAGGREGATE関数の違い

SUBTOTALの上位互換としてAGGREGATE関数があります。エラー値や非表示行の扱いをより細かく制御できます。

=AGGREGATE(3, 5, A2:A100)

関数	特徴	対応バージョン
SUBTOTAL	シンプルで使いやすい。基本これでOK	すべてのバージョン
AGGREGATE	エラー値の無視など細かい制御が可能	Excel 2010以降

通常はSUBTOTALで十分ですが、エラー値が混在するデータを扱うならAGGREGATEが便利です。