COUNTIF関数で空白以外をカウントする方法！COUNTAとの使い分けと空文字の罠

ExcelのCOUNTIF関数で「空白以外」をカウントしたいときはないでしょうか。

けど、そんな中で悩むことは、

・空白セルを除いて件数を数える方法がわからない
・COUNTIF関数で「空白以外」を指定しても件数が合わないことがある
・COUNTA関数との使い分けがわからない

ですよね。

今回はそんなお悩みを解決する

・COUNTIF関数で空白以外をカウントする方法（COUNTA・SUMPRODUCTとの使い分け）

についてまとめます！

完成イメージ

名簿の「備考」のように、空欄が混じった列の件数を数えたい場面はよくあります。
今回は
・COUNTIF関数で空白以外のセルをカウントする方法
・空文字（数式が「””」を返すセル）が混じると件数がズレる理由と対処法
をゴールにします。

上の画像が完成形です。真の空白と空文字の違いを押さえ、空白以外の件数を正しく数えられるようになります。

数式は =COUNTIF(C2:C13,”<>“) と =SUMPRODUCT((範囲<>“”)*1) を使い分けます。
なお、毎回数式を入れるのが手間な方向けに VBAで一括カウントする方法 と、うまくいかないときの対処法（トラブルシュート） もあわせてご紹介します。
早速実装して試してみましょう！

STEP1. 名簿のサンプルデータを用意する

まずは数える対象になる名簿を用意します。
1行目が見出しで、2行目以降がデータです。
「備考」列（C列）には空欄が混じっています。
この空欄を除いた件数を数えていくのが、今回のゴールです。
下の表をそのままコピーして、Excelのシートに貼り付ければ同じ状態を再現できます。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

氏名	部署	備考
佐藤健	営業部	新規開拓
鈴木花子	総務部
高橋大輔	営業部	既存客フォロー
田中美咲	経理部
伊藤翔	営業部	新規開拓
渡辺由紀	総務部
山本涼	営業部	既存客フォロー
中村彩	経理部
小林直樹	総務部	新規開拓
加藤恵	営業部
吉田拓真	経理部	既存客フォロー
森亜美	営業部

※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。

STEP2. COUNTIF関数で空白以外を数える

空白以外の件数を数えるには、COUNTIF関数の条件に「<>」を使います。
数式を入れるセル（ここでは G17セル）を選び、次のように入力します。

=COUNTIF(C2:C13,"<>")

POINT

「空白以外」は “<>“ で指定します。
=COUNTIF(範囲,”<>“) と覚えてしまえばカンタンですね。

実際の入力画面がこちらです。
入力中は引数のヒントが表示されるので、順番を確認しながら進められます。

STEP3. COUNTA関数との使い分け

空白以外を数える関数といえば、COUNTA関数もあります。
COUNTA関数は「値が入っているセルの個数」を数える関数です。
真の空白セルだけが混じっている今回は、COUNTIF関数と同じ結果になります。

=COUNTA(C2:C13)

空白セルの個数を出したいときは、逆にCOUNTBLANK関数を使います。

真の空白しかない列では結果がそろう

次の数式は、いずれも「6」を返します。

数式	結果	意味
=COUNTIF(C2:C13,”<>”)	6	空白以外のセルを数える
=COUNTA(C2:C13)	6	値が入っているセルを数える
=COUNTBLANK(C2:C13)	6	空白セルを数える（COUNTAの逆）
=SUMPRODUCT((C2:C13<>””)*1)	6	空白以外を数える（空文字も除外）

実際に入力した画面がこちらです。

STEP4. 「見た目は空欄」でも中身は空文字のことがある

ここからが今回のポイントです。
名簿の備考列は、別の表から数式で転記しているケースがよくあります。
その場合、該当がない行は =IF(C17=””,””,C17) のような数式になります。
すると、見た目は空欄でも中身は「””」（空文字）になります。
見た目は同じでも、真の空白と空文字は別物です。

空白セルと空文字の違い

判定	真の空白（Deleteしたセル）	空文字（数式が””を返すセル）
見た目	空欄	空欄（同じに見える）
ISBLANK関数	TRUE	FALSE
COUNTA関数	数えない	数えてしまう
COUNTBLANK関数	数える	数える
COUNTIF(範囲,”<>”)	数えない	数えてしまう
SUMPRODUCT((範囲<>””)*1)	数えない	数えない

実際に、備考を数式で転記している名簿がこちらです。

STEP5. 空文字が混じるとCOUNTIFは件数を誤る

数式で転記している備考列（D17:D22）に、同じ数式を入れてみます。
COUNTIF(D17:D22,”<>“) は「6」を返します。
しかし、本来数えたい件数は「3」です。
空文字のセルまで「空白以外」として数えてしまうためです。
条件を COUNTIF(範囲,”<>“&””) に変えても、結果は6のまま変わりません。

POINT

COUNTIF(範囲,”<>”) は空文字（数式の””）を空白とみなしません。
COUNTA関数も同じで、空文字を数えてしまいます。

実際の結果がこちらです。

STEP6. SUMPRODUCT関数で空白以外を正確に数える

空文字も除外して数えたいときは、SUMPRODUCT関数を使います。

=SUMPRODUCT((D17:D22<>"")*1)

「D17:D22<>””」で空白以外を判定し、TRUE/FALSEを「*1」で1と0に変換して合計します。
配列数式（Ctrl+Shift+Enter）は不要で、そのままEnterで確定できます。
実際の入力画面がこちらです。

VBAで空白以外を一括カウントする

毎回数式を入れるのが手間なときは、VBAで一括カウントするのもおすすめです。
Len関数で文字数を調べると、真の空白も空文字もまとめて除外できます。

Sub CountNotBlank()
    Dim rng As Range, c As Range, n As Long
    Set rng = Range("D17:D22")
    For Each c In rng
        If Len(c.Value) > 0 Then n = n + 1
    Next c
    MsgBox "空白以外は " & n & " 件です"
End Sub

タカヒロ

ポイントは Len(セル) > 0 の判定です。
「<>」ではなく文字数で調べるのがミソで、空文字もきちんと除外できます。
むずかしく感じますが、判定式は1つだけなのでカンタンですね。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったように動かないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

症状	原因	対処
COUNTIFの件数が多すぎる	数式が「””」を返すセルが混じっている	SUMPRODUCT((範囲<>””)*1) に置き換える
見た目は空欄なのに数えられる	全角スペースや改行だけが入っている	=LEN() で長さを確認し、SUBSTITUTEで除去する
COUNTAとCOUNTIFの結果が違う	範囲の指定がズレている／空文字の有無	範囲を C2:C13 のように固定して見直す
セル結合があると数が合わない	結合セルは左上のセルにしか値が入らない	結合を解除してから数える

関数の説明

今回使う4つの関数の概要です。
どれも件数を数える基本の関数なので、違いを押さえておきましょう。

COUNTIF関数（カウントイフ）

書式	=COUNTIF(範囲, 検索条件)
できること	指定した範囲のうち、条件に一致するセルの個数を数えます。
戻り値	条件に一致したセルの個数（数値）
こんなときに	「空白以外」なら “<>”、「100以上」なら “>=100” のように条件を指定して数えたいとき。
注意	空文字（数式の””）は空白とみなされません。空文字も数えてしまうため、件数がズレる原因になります。

COUNTA関数（カウントエー）

書式	=COUNTA(値1, [値2], …)
できること	範囲のうち、値が入っているセルの個数を数えます。
戻り値	空白でないセルの個数（数値）
こんなときに	条件を指定せず、入力済みのセルを数えたいとき。
注意	数式が返す空文字も「値あり」として数えます。真の空白だけを除外したいときに使います。

COUNTBLANK関数（カウントブランク）

書式	=COUNTBLANK(範囲)
できること	範囲のうち、空白セルの個数を数えます。COUNTA関数の逆の動きです。
戻り値	空白セルの個数（数値）
こんなときに	未入力の件数をチェックしたいとき。
注意	空文字も「空白」として数えます。COUNTAと足しても全体の件数にならないことがあります。

SUMPRODUCT関数（サムプロダクト）

書式	=SUMPRODUCT(配列1, [配列2], …)
できること	配列どうしを掛けて合計します。条件式を「1」で数値化すると、空白以外のセルを正確に数えられます*。
戻り値	計算結果（数値）
こんなときに	空文字（数式の””）を除外して、空白以外の件数を数えたいとき。
注意	配列数式（Ctrl+Shift+Enter）は不要です。そのままEnterで確定できます。