Excel COUNTIF関数の使い方｜件数・文字一致・重複を正しく数える

※本記事にはPRを含む場合があります。

こんにちは。『脱会社員』脱サラを応援し続けるブログ、運営者の『なかびー』です。

Excelで「受付済みが何件あるか」「同じ番号が何回出てくるか」を知りたいときは、COUNTIF関数が役立ちます。基本の書き方は「=COUNTIF(範囲,条件)」です。指定した範囲の中から、一つの条件に合うセルの数を返します。名簿、申込一覧、問い合わせ管理など、件数を確認する仕事へ使えます。

気を付けたいのは、返る数字が必ずしも人数や注文数になるわけではないことです。同じ人が二回申し込んだ表なら二つのセルが数えられますし、履歴を一行ずつ残した一覧なら同じIDが何度も登場します。この記事では、十行の架空申込表を使い、文字一致、数値条件、重複、空欄、複数条件を区別しながら、説明できる件数を作ります。

仕様の根拠は2026年10月4日に確認したMicrosoft公式資料です。Windows・Mac・Web、製品の版によって画面や細かな操作は異なる場合があります。数式は半角記号を使った一般的な日本語環境向けで、期待件数は独立した別計算でも照合しています。Excel実機で全環境を検証したものではありません。名簿は架空例、掲載画像は説明用の生成イメージです。

COUNTIFで数える「一件」を決め、申込一覧を準備する

セルの個数と人数は、同じとは限らない

COUNTIFは、指定した範囲にあるセルを条件で判定します。受付という文字が六つのセルに入っていれば、受付を条件とした結果は6です。その六つが別々の人なのか、一人が複数回登録したものなのかは、関数だけでは判断しません。最初に、表の一行が申込、人物、問い合わせ、更新履歴のどれなのかを確かめましょう。

例えば講座の申込一覧で、一人が入門と実践の両方へ申し込む仕組みなら、申込件数は二件でも人数は一人です。一方、一つの申込を修正するたびに履歴を追記する表なら、申込IDが同じ行をまとめて現在の状態を確認する作業が必要になります。COUNTIFの答えへ「人」と書く前に、数えている単位を決めてください。

今回の記事で扱う中心は、条件に合う記録の件数です。重複の検出は行いますが、重複が見つかった記録を自動で削除する手順には進みません。何を一件とするかを確認するための数式として使い、一覧の正本をどう修正するかは、そのデータを管理する人が判断できる状態にします。

十行の架空データを使って答えを先に用意する

A1:E1へ「申込ID・講座・状態・受付経路・希望回数」と入力し、A2:E11へ下表の値を置きます。希望回数は数値として入力してください。IDは説明用で、実在する申込者を指しません。同じIDを二組入れているのは、後半で重複の調べ方を練習するためです。

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

状態別の記録数は、受付6、取消2、確認中2で、合計10です。講座別では入門5、実践5。受付経路はWeb6、メール4です。この段階では、R101やR104が繰り返されていても、表に存在する十行をそのまま数えます。修正すべき重複かどうかの判断と、現在の表を正しく数えることを分けて進めます。

最初は自分で全行を見渡せる件数にしてください。大きな実務ファイルから始めると、関数が合っているか確認するために別の集計が必要になり、原因を追いにくくなります。架空の小さな表で式と期待値が一致した後に、実際のデータの単位と入力ルールを調べて適用する順序が安全です。

基本の二つの引数を読み分ける

受付の記録数は、C2:C11を範囲、受付を条件として指定します。「何を探すか」が文字の場合は、式の中で半角のダブルクォーテーションで囲みます。結果が6になったら、C2、C3、C4、C7、C8、C10の六つが該当していることを、元表で確かめてください。

=COUNTIF(C2:C11,"受付")

範囲には見出しのC1や、将来使うかもしれない空欄を無制限に含めず、まず練習の十行だけを指定します。C2:E11のように複数列を渡せば、行ではなくその範囲のセルが判定対象になります。複数列に同じ言葉がある表では、一行が一度だけ数えられるとは限りません。件数を取る列を一つ決めることから始めましょう。

構文、条件の記号、文字の比較の注意はMicrosoft公式のCOUNTIF解説で確認できます。公式の例を読んだ後も、自分の表では何列が条件列かを読み替える必要があります。セル番地だけをコピーして結果が出たことを、業務への適用完了としないようにしてください。

条件セルを作り、入力と数式の役割を分ける

G1へ「調べる状態」、G2へ受付、H1へ「記録数」と入力し、H2に次の式を置きます。G2を取消へ変えれば2、確認中へ変えても2になります。数式を毎回編集するより、条件の置き場所を一つにする方が、使う人も現在の条件を確認しやすくなります。

=COUNTIF($C$2:$C$11,G2)

G2を引用符で囲んだ「"G2"」は、セルの内容を使う書き方ではありません。セル参照と直接入力の文字を区別してください。また、G2が未入力のときに何を表示するかも決めます。空欄を調査する場合以外は、条件を選ぶ前の結果を業務上の件数として扱わない表示にする方が、誤読を減らせます。

複数の状態をG2:G4へ並べるなら、H2の式を下へコピーします。数える範囲は固定し、条件セルだけG2からG3、G4へ動かします。コピー後のH4を開いてC2:C11が変わっていないか確かめましょう。範囲がC4:C13へずれても、たまたま同じ件数になることがあるため、値だけでなく式の参照先を見る必要があります。

文字・部分一致・数値の条件を使い分ける

完全な文字一致と、含まれる文字を分ける

「受付」を条件にした式は、状態が受付であるセルを数えるためのものです。備考欄に「受付後に確認」と書かれたセルを探したいなら、別の条件の設計が必要になります。短い状態コードを管理する列と、自由記入の説明文の列を混ぜないことが、数え方を分かりやすくする出発点です。

部分一致では、*を任意の長さの文字列として使えます。「受付*」は受付から始まる文字列、「*受付」なら受付で終わる文字列、「*受付*」は途中も含めて受付という並びを持つ文字列を探す条件です。便利な反面、「未受付」も*受付*へ当てはまるため、欲しい意味と一致するかを先に確認してください。

検索対象が状態の列なら、曖昧な部分一致より、決めた候補の正確な一致を使う方が管理しやすいことがあります。例えば「未受付・受付・取消」の三状態を含む列へ*受付*を指定すると、未受付も増えてしまいます。式が正しく文字を見つけても、依頼された集計の意味に合うとは限りません。

ワイルドカード自体を探すときは記号を区別する

別の練習シートのA2:A6へ、A01、A02、A*1、A?1、BA01を順番に入力します。ここでは*と?が実際の文字として入っているセルも含めました。条件A*ならAから始まる四つ、*A*ならAを含む五つが対象です。まず普通の文字列と検索用の記号を意識して見比べましょう。

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

記号を含む管理番号を条件セルから参照するときも注意が必要です。条件セルへA*1を入れたから必ず文字どおりの完全一致になる、と決めつけないようにします。実務で*や?を使うコード体系なら、どのように条件を作るかを別の検査表で試してください。手入力の名前や自由文と、厳密なコード照合を同じ感覚で扱わないことが大切です。

ワイルドカードの基本的な使い方はMicrosoftのCOUNTIF活用記事でも確認できます。本稿の五つのコードは検算しやすいように独自に組んだ例です。実データの内容を外部サービスへ送って試す必要はなく、記号だけを模した架空コードで十分に動きを確かめられます。

数値条件は、何回以上かを記号で表す

元の申込表へ戻り、希望回数が2以上の記録数を求めます。対象はR102、R104の二行、R106の計四行です。この式は希望回数を足すものではなく、2以上という条件を満たすセルを四つ数えています。列の値と、返ってきた件数の単位を分けて読みましょう。

=COUNTIF(E2:E11,">=2")

「2より大きい」に変えるなら条件は">2"で、3のR106だけが残り、結果は1です。条件の境界にある2を含むかどうかで、結果が大きく変わる例です。以上・以下・超える・未満という依頼の言葉を、そのまま一度文章で確認してから記号へ置き換えると間違えにくくなります。

基準をG6へ置く場合は「">="&G6」のように、比較記号とセルの中身をつなぎます。G6に2を入れると4、3に変えると1です。G6へ「2回」と文字を含めて入力するのではなく、数値の2を入れて表示形式や見出しで単位を示してください。入力欄が空の状態を、正式な条件として数えない運用も必要です。

大文字と小文字を区別する用途を見極める

COUNTIFの文字条件は英字の大文字と小文字を区別しません。例えばAb12、ab12、AB12、X003をA2:A5へ置き、Ab12を数えると、最初の三つが同じ条件に当てはまる扱いになります。人名や一般的な状態名では問題にならなくても、文字の大小を区別する識別子には注意が必要です。

大小を区別する照合が必要なら、EXACTのような別の比較手段を検討します。EXACTは文字列を比較する関数で、Microsoft公式のEXACT解説で仕様を確認できます。上の四セルなら、隣の列へ=EXACT(A2,"Ab12")を入れて下へコピーし、TRUEになる行を確認する練習ができます。

関数を組み合わせて一式で数える方法もありますが、初心者はまず比較結果を別列で見えるようにすると原因を追いやすくなります。厳密なコード照合を頼まれた場合は、大小、空白、全角半角、先頭の0、記号の意味を事前に確認してください。「同じに見える」ことと「管理上同じとする」ことを混同しないようにしましょう。

COUNTIFで重複を見つけ、削除の前に意味を確認する

同じIDが何回現れるかを各行へ表示する

元の申込表のF1へ「ID出現回数」と入力し、F2に次の式を置いてF11までコピーします。範囲A2:A11は固定し、調べるIDのA2だけを行ごとに動かします。R101の行とR104の行は2、それ以外のIDは1になることを確認してください。

=COUNTIF($A$2:$A$11,A2)

この結果は、その行のIDが一覧全体に何回あるかを表します。F列で2以上を探すと、R101の二行とR104の二行が見つかります。ただし、ここで見つかるセルは四つです。「重複しているIDの種類が四つ」とは違うため、報告書の見出しを「重複候補行数」など、数えた意味に合うものへそろえます。

入力欄を多めに用意している表では、IDが空欄の行にも式をコピーすると、空欄同士が数えられて大きな数になることがあります。未入力行へ表示を出したくないなら、例えば「=IF(A2="","",COUNTIF($A$2:$A$11,A2))」のように先に未入力を分ける方法があります。これは表示の設計であり、入力漏れが解消されたことにはなりません。

重複候補の四行と、余分な二行を区別する

十行の表にあるIDは、R101からR108までの八種類です。R101は二回、R104も二回現れ、重複に関係する行は四行あります。一方、「各IDを一度だけ残すと何行減るか」という意味なら、二行分です。同じ表でも、何を重複件数と呼ぶかによって2にも4にもなることを明確にしましょう。

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

上の異なるIDの種類数は、今回は八つを手で並べて確認しています。COUNTIF一つに「重複を除いた人数」を自動で求める意味が含まれているわけではありません。求める単位に合う方法を別途選び、場合によってはUNIQUEなどの対応関数や他の集計手段を検討します。その前に、一IDが一人を表すかどうかを確かめる必要があります。

もしR101が同じ人の別講座への二つの申込なら、重複IDだけで一行を消すと正しい申込を失います。今回のR101は講座・状態・経路も同じですが、同じ内容だから誤登録と確定できるわけではありません。受信日時や元の申込票など、管理している一次記録へ戻って判断してください。

先頭から何回目かを調べると、再登場を追いやすい

全体での出現回数とは別に、先頭から現在行までに何回現れたかを表示すると、二度目以降の登場を探せます。I1へ「ここまでの出現回数」、I2に次の式を置いてI11までコピーします。開始の$A$2は固定し、範囲の終点A2はA3、A4へ広がるようにするのがポイントです。

=COUNTIF($A$2:A2,A2)

この表では、R101が初めて出る2行目は1、再登場する4行目は2です。R104も6行目は1、7行目は2になります。全体の出現回数が2と出た四行のうち、再登場した行は二つだと確認できます。これは現在の並び順での初回・再登場であり、日時として最も古い・最も新しいことまで証明するものではありません。

表を並べ替えると、どの行が初回と表示されるかが変わる可能性があります。最新記録を残す作業に使うなら、更新日時や履歴の規則を確認したうえで、何を最新と呼ぶかを定めなければなりません。COUNTIFの順序依存の結果だけで、どの行を残すかを決定しないことが大切です。

同じIDで状態が違うときは履歴か誤りかを調べる

R104の二行は、片方が確認中、もう片方が受付です。この表が更新履歴なら、確認中から受付へ進んだ記録として両方を保持している可能性があります。一方、現在の状態だけを一行に持つ台帳なら、古い行が残った重複かもしれません。同じ数式で2と出ても、必要な対応は表の設計で変わります。

調査用に、ID、講座、状態、元の行番号、受信時刻などを一緒に見比べます。重複の候補だけを別シートへ写す場合も、原表のどの行へ戻れるかを残してください。コピーした表の一部だけを直すと、元表との対応が分からなくなることがあるため、正本の修正担当と修正手順を決めます。

削除が必要と判断された場合も、変更前のファイルと、除外した理由を保存する運用が役立ちます。本記事で示すCOUNTIFは判断材料を出す段階までです。重複表示が付いたものを機械的に消してよいという合図ではありません。取消記録や監査上必要な履歴を、件数を合わせるためだけに消さないようにしましょう。

空欄・ゼロ・複数条件で件数がずれる原因を切り分ける

空白に見えるセルが、何も入っていないとは限らない

空欄の確認では、未入力のセル、数式が空文字列を返すセル、半角スペースが入ったセルを区別します。画面上は似ていても、数式で数えた結果は目的や関数によって変わります。「何も見えないものを数える」のか「一度も入力されていないセルを数える」のかを決めずに式だけを選ばないことが重要です。

練習用の別列J2:J5で、J2は何も入力しない、J3は=""という式、J4は半角スペース一文字、J5は数値0として比較できます。COUNTBLANKは空セルに加えて、空文字列を返す数式も数えるため、この例ではJ2とJ3の二つが対象です。J4のスペースは文字があり、J5の0は数値なので、それぞれ別の状態です。

COUNTBLANKの扱いはMicrosoft公式のCOUNTBLANK関数で確認できます。COUNTIFの条件を""にする方法を使うときも、実際の表にある未入力・空文字列・スペースを小さな試験で分けてください。空欄の件数をゼロに見せるため、未確定の値を適当に埋める修正は避けましょう。

数字の0を、未入力の代わりにしない

本例の希望回数には0が二つあります。取消の行へ説明用に置いた数値であり、空欄ではありません。もし0が「希望なし」、空欄が「未確認」という業務なら、両方を同じ分類として扱うと未確認の記録を見落とします。入力ルールで0が何を意味するかを決めておく必要があります。

数値条件を使った結果が予想と違う場合は、セルが数値か文字列かも確認します。数字に見えるIDや郵便番号は、先頭の0を残すため文字列として持つ方が適した場合があります。一方、回数を比較する列は数値へそろえる設計が分かりやすいでしょう。列ごとの意味を考えずに、ブック全体を一括で数値変換しないでください。

先頭の0が必要なコードを練習するときは、入力前に文字列の列として準備します。すでに0が失われている場合、表示形式で桁を補っても元のコード体系を復元できたとは限りません。元のデータと照合して必要な桁を確認してください。誤ったコードが同じに見えて重複する問題は、COUNTIFの条件を変えるだけでは解決しません。

二つの条件を同時に満たすならCOUNTIFSを検討する

入門で、かつ受付の記録数を求めるなら、講座の列と状態の列を一緒に判定する必要があります。COUNTIFに条件を無理に追加するのではなく、複数条件用のCOUNTIFSを使う方法があります。範囲と条件を対で並べ、すべての条件に合う行を数えます。

=COUNTIFS(B2:B11,"入門",C2:C11,"受付")

結果は4です。該当するのは2、4、8、10行目で、入門の取消である5行目は対象になりません。B2:B11とC2:C11は同じ十行に対応させます。長さだけ同じでも一方が一行ずれていれば、講座と状態の組合せを誤って判定してしまうため、最初と最後の行を見比べてください。

複数条件の構文はMicrosoft公式のCOUNTIFS関数で確認できます。「入門の件数5」と「受付の件数6」を足した11は、両方を満たす件数ではありません。「かつ」と「または」を依頼者の言葉で確認してから数式を選びましょう。

OR条件を足す場合は、重なりを二度数えない

同じ状態列で「受付または確認中」を数えるなら、現在の表では一つのセルが両方の値を同時に持たないため、受付6と確認中2を足して8になります。状態が一つだけ入る列であることが前提です。自由文へ受付・確認中の両方が書かれる表では、この前提をそのまま使えません。

=COUNTIF(C2:C11,"受付")+COUNTIF(C2:C11,"確認中")

「入門または受付」は、入門5と受付6を単純に足すと、入門かつ受付の4行を二度数えます。重なりを引いて5＋6−4＝7です。式ならCOUNTIFでそれぞれを数え、COUNTIFSで両方に当てはまる件数を引きます。今回の十行を丸で囲むように確認すると、条件の関係が見えやすくなります。

=COUNTIF(B2:B11,"入門")+COUNTIF(C2:C11,"受付")-COUNTIFS(B2:B11,"入門",C2:C11,"受付")

条件が増えて読みづらくなったら、対象かどうかを一行ずつ示す補助列を用意する方法もあります。長い式を一つへまとめることより、どの記録が対象なのかを担当者が説明できる方が重要です。結果が一覧の総行数10を超えていたら、複数セルを数えているのか重複加算なのか、数える単位へ戻って確認します。

件数の検算と更新ルールを残し、仕事で使える集計にする

全体件数へ戻る分類で、漏れを確認する

状態が必ず受付・取消・確認中のいずれかで、空欄も他の値もないなら、状態別件数の合計は全体10へ戻るはずです。本例では6＋2＋2＝10なので一致します。ただし、同じ合計になったことだけですべて正しいとは断言できません。一つの分類で一件多く、別の分類で一件少ない場合は、合計だけが合うこともあります。

まず分類ごとの代表行を見て、次に全体へ戻るかを確かめるという二段階で検算します。状態に「受付済」といった別の表記が一つ混ざれば、決めた三分類だけでは合計が9になるなど、漏れを発見するきっかけになります。入力候補を変更した月は、以前の値が過去の行へ残っていないかも見てください。

分類外を探すときは、残った行を具体的に調べることが必要です。単に10から分類合計を引いた1という数字を「その他」として放置すると、入力ミスか新しい状態かが分かりません。担当者へ確認して分類を追加するのか入力を直すのかを決め、分類ルールを更新します。

表示中の行だけを数えたい場合は別の設計にする

COUNTIFは、通常の範囲指定のままでは、フィルターで隠れた行を自動的に集計対象から外すための関数ではありません。画面でWebだけを表示していても、式の範囲が全十行なら、その範囲に基づいて判定します。見えている行数とCOUNTIFの結果が違っても、ただちに計算故障とは限りません。

一時的な画面の絞り込みと、式へ明示した条件を分けて管理しましょう。Webの受付数が必要なら、受付経路がWebかつ状態が受付という条件を数式に持たせる方法を検討できます。見えている行そのものを基準にしたい業務では、SUBTOTALなど表示行を扱う仕組みを調べ、自分の目的に合うものを選ぶ必要があります。

報告用の集計表には「集計条件は上の入力欄。元表の表示フィルターとは独立」などの短い注記を残すと誤解を減らせます。フィルターを使った人が画面の件数だけで結果を書き換えないよう、どちらを正本とするかを伝えておくことが大切です。

行追加とコピー後は、先頭・末尾・例外を試す

COUNTIFの範囲がC2:C11なら、12行目へ受付を追加しても、式の範囲外にある限り結果は6のままです。継続利用する一覧では、Excelテーブルへ変換して構造化参照を使う方法や、追加時に範囲を確認する手順を用意します。自動で広がることを期待するだけではなく、追加した行が実際の参照に入ったかを検査してください。

例えば申込表を「申込一覧」というテーブルにし、状態の列を使うなら「=COUNTIF(申込一覧[状態],"受付")」と表せます。行がテーブルへ追加されていることを確認し、練習用コピーで受付を一行足したら7へ増えるか試します。その後、その行を取消へ変えれば受付は6へ戻り、取消は3へ増えるはずです。

Microsoft公式の構造化参照の説明にはテーブル名と列名を使う方法があります。見出しを変える、列を移動する、別のファイルへコピーするといった変更後も、代表の条件で再確認してください。前月動いたことは、今月の追加行まで正しく取り込めた証明にはなりません。

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

引き継ぎには件数の定義と例外時の確認先を書く

集計ファイルには、「数えている単位は申込記録。人数ではない」「重複IDは調査対象として残す」「状態欄の三分類以外は確認する」といった定義を残します。作成者の頭の中だけにある前提は、担当が変わると伝わりません。結果の数字の隣に短い注記を置き、詳しい説明は別の手順欄へまとめると読みやすくなります。

更新時の流れは、受領した元ファイルを保存し、取込件数を確認し、IDと状態の欠損を見て、集計し、分類合計と対象行を照合する順にすると整理できます。どの版を確認したか分かるように、対象日、ファイル名、確認者を残してください。実際の個人情報を含む名簿は、依頼元の保存・共有ルールに従って取り扱います。

管理表の項目や書式から見直す場合は、当サイトのbizoceanの業務テンプレート活用ガイドも参考になります。ひな形に用意された状態名やIDの列が、自分の業務の一件と合うかを確認してから使いましょう。テンプレートの式が入っていても、件数の意味の確認まで省略することはできません。

COUNTIFを仕事へ取り入れる最初の目標は、短い式を暗記することより、「この6は何の六件か」を説明できることです。十行の練習表で受付6、入門かつ受付4、入門または受付7を確かめ、重複候補の四行と超過二行を区別してみてください。対象の定義と検算を一緒に残すと、次の更新でも迷いにくい集計になります。