Excelで売上、点数、在庫数、期限日などを管理していると、指定した数値以上のデータ、あるいは指定値以下のデータが何件あるのかを知りたい場面があります。
このような集計には、条件を1つ指定できるCOUNTIF関数と、複数の条件を組み合わせられるCOUNTIFS関数が便利です。
たとえば80点以上の人数、100個以下の在庫数、特定の期間内で目標金額以上になった件数などを、数式だけで素早く数えられます。
以上を数える場合は、比較演算子の「>=」を条件に入れることが基本です。
以下を数える場合は「<=」、範囲内を数える場合はCOUNTIFS関数で条件を2つ設定します。
この記事では、1行目を見出し行としたサンプルデータを使い、以上・以下・指定範囲・複数条件の件数をカウントする方法を詳しく解説します。
エクセルで以上の件数を数えるCOUNTIF関数
それではまず、指定した数値以上の件数をCOUNTIF関数で数える方法について解説していきます。
| A列 | B列 |
|---|---|
| 氏名 | テスト点 |
| 田中 | 92 |
| 佐藤 | 76 |
| 鈴木 | 80 |
| 高橋 | 68 |
| 伊藤 | 85 |
80点以上を数える基本数式
テスト点が入力されたB2からB6のうち、80点以上の人数を数える場合は、結果を表示したいセルにCOUNTIF関数を入力します。
=COUNTIF(B2:B6,”>=80″)
この数式では、B2:B6が検索する範囲、”>=80″が80以上という条件です。
条件に使う比較演算子と数値は、必ず同じ二重引用符の中にまとめて入力します。
サンプルでは92、80、85の3件が条件に合うため、数式の結果は3になります。
80点ちょうどの鈴木さんも含まれる点が、「>」ではなく「>=」を使う重要なポイントです。
【操作のポイント】以上を表す「>=」は、記号を逆の順番にせず、比較演算子と基準値を二重引用符で囲みます。
セル参照で基準値を変更する設定
基準となる数値を毎回数式の中で書き換えるより、別のセルに入力して参照する方法が実務では便利です。
たとえばD2セルに80と入力し、B列の点数がD2以上である件数を数えるなら、次の数式を使います。
=COUNTIF(B2:B6,”>=”&D2)
「>=」とD2をアンパサンドでつなぐことで、Excelは条件を「>=80」として扱います。
条件の数値をセルに置く場合、比較演算子だけを二重引用符で囲み、セル番地と連結します。
D2の値を70や90に変更すれば、数式を書き直さなくても該当件数が自動更新されます。
合格ライン、予算額、最低在庫数など、基準値が変動する一覧では特に使いやすい設定です。
【操作のポイント】基準値を入力するセルと計算結果のセルを分けると、集計条件を見直しやすくなります。
文字列や空白セルを含む範囲の注意点
COUNTIF関数は指定範囲内の条件に一致するセルだけを数えるため、空白セルは通常カウントされません。
ただし、数値に見えても文字列として保存されているデータが混ざると、想定どおりに件数が出ないことがあります。
たとえば先頭にアポストロフィが付いている数値、外部システムから取り込んだ文字列形式の点数などは確認が必要です。
数値が左寄せで表示されている場合は、文字列として入力されている可能性があります。
必要に応じてセルのエラー表示から数値に変換する、VALUE関数を使う、データ形式を統一するといった対処を行いましょう。
集計前にデータの種類をそろえることが、COUNTIF関数を正確に使うための土台です。
【操作のポイント】件数が0になる場合は、条件式だけでなく、対象セルが数値として認識されているかも確認します。
エクセルで以下の件数を数えるCOUNTIF関数
続いては、指定した数値以下のデータをCOUNTIF関数で集計する方法を確認していきます。
| A列 | B列 |
|---|---|
| 商品名 | 在庫数 |
| 商品A | 120 |
| 商品B | 48 |
| 商品C | 100 |
| 商品D | 35 |
| 商品E | 76 |
100個以下を数える基本数式
在庫数が100個以下の商品数を集計する場合は、以下を意味する「<=」を条件に設定します。
=COUNTIF(B2:B6,”<=100″)
この例では48、100、35、76の4件が該当するため、結果は4です。
「<=100」は100未満ではなく、100を含めた100以下を意味します。
100を含めずに数えたいときは、「<100」に変更してください。
在庫切れの警告対象、納期までの日数、目標未達の件数などを把握する場面で役立つ数式です。
【操作のポイント】境界の数値を含めるかどうかを決めてから、「<」と「<=」を使い分けます。
日付が指定日以前の件数
COUNTIF関数は数値だけでなく、日付の比較にも使えます。
Excelでは日付が内部的に連続した数値として管理されているため、期限日が指定日以前の件数も同じ考え方で求められます。
たとえばB2:B20に期限日があり、D2に確認したい基準日が入力されている場合は、次の数式です。
=COUNTIF(B2:B20,”<=”&D2)
D2には実際の日付を入力し、文字列ではなく日付形式として認識されていることを確認します。
「今日以前」を数えたい場合は、D2の代わりにTODAY関数をつないで指定できます。
=COUNTIF(B2:B20,”<=”&TODAY())
期限切れや当日締切の案件を見つける集計に向いています。
【操作のポイント】日付を比較する場合は、対象列と基準セルの両方を日付として入力し、表示形式だけで判断しないことが大切です。
空白を除外して以下を数える条件
通常の数値データではCOUNTIFだけで問題ありませんが、空白セルや未入力セルの扱いを明確にしたい場合があります。
とくに0以下を数えるときは、空白セルが意図しない判定にならないか、実データで結果を確認しましょう。
数値が入力されているセルだけを対象にしたい場合は、COUNTIFS関数で「空白ではない」という条件を追加する方法があります。
=COUNTIFS(B2:B20,”<=100″,B2:B20,”<>”)
「<>」は空白ではないことを表す条件です。
同じ列に対して数値条件と空白除外条件を重ねると、入力済みの数値だけをより明確に集計できます。
未確定データが多い台帳では、このような条件追加が集計ミスの予防になります。
【操作のポイント】空白を除外する必要があるかは、元データに未入力行が混在するかどうかで判断します。
指定範囲内の件数を数えるCOUNTIFS関数
続いては、ある数値以上かつ別の数値以下という範囲指定をCOUNTIFS関数で集計する方法を確認していきます。
| A列 | B列 |
|---|---|
| 社員名 | 月間売上 |
| 山田 | 320000 |
| 中村 | 470000 |
| 小林 | 500000 |
| 加藤 | 610000 |
| 吉田 | 390000 |
30万円以上50万円以下の数式
30万円以上かつ50万円以下の売上件数を数える場合、COUNTIFS関数で同じ範囲に対して2つの条件を指定します。
=COUNTIFS(B2:B6,”>=300000″,B2:B6,”<=500000″)
COUNTIFS関数は、条件範囲と条件をセットで順番に入力する仕組みです。
このサンプルでは320000、470000、500000、390000の4件が該当します。
COUNTIFS関数では、すべての条件を満たしたデータだけがカウントされます。
片方だけに該当する610000は上限を超えているため、結果には含まれません。
【操作のポイント】範囲内集計では、下限条件と上限条件の対象範囲を同じ大きさにそろえます。
下限と上限をセル参照する数式
基準値が頻繁に変わる表では、下限をD2、上限をE2のように別セルへ入力しておくと管理しやすくなります。
=COUNTIFS(B2:B6,”>=”&D2,B2:B6,”<=”&E2)
D2が300000、E2が500000なら、先ほどと同じ範囲を集計できます。
条件の数値をセル参照にすると、会議用の資料や月次レポートで判定範囲を変更する作業が短縮されます。
下限セルと上限セルには、通貨記号付きの文字列ではなく数値を入力することが基本です。
表示だけを円単位に整えたい場合は、セルの表示形式で対応しましょう。
【操作のポイント】基準値セルには「下限」「上限」とラベルを付け、数式を確認しやすい表に整えます。
以上未満と超過以下の使い分け
数値の区切り方によっては、境界値を重複させずに分類したいことがあります。
たとえば30万円以上50万円未満の区分を作るなら、上限条件は「<500000」とします。
=COUNTIFS(B2:B6,”>=300000″,B2:B6,”<500000″)
次の区分を50万円以上にすれば、500000のデータはどちらか一方だけに入ります。
複数のランクを作るときは、「以上かつ未満」で区切ると二重計上を防ぎやすくなります。
売上帯、年齢帯、点数帯、在庫ランクを作成するときに有効な考え方です。
【操作のポイント】連続する区分では、境界値をどちらに含めるかを最初に統一しておきます。
複数条件で以上・以下を数えるCOUNTIFS関数
続いては、担当者や分類などの条件も加えながら、以上・以下の件数をCOUNTIFS関数で数える方法を確認していきます。
| A列 | B列 | C列 |
|---|---|---|
| 担当者 | 商品区分 | 売上 |
| 田中 | A | 125000 |
| 佐藤 | B | 98000 |
| 田中 | A | 160000 |
| 田中 | B | 142000 |
| 佐藤 | A | 175000 |
担当者と売上条件を組み合わせる数式
田中さんが担当し、かつ売上が120000以上である件数を求める場合は、担当者列と売上列に別々の条件を設定します。
=COUNTIFS(A2:A6,”田中”,C2:C6,”>=120000″)
条件に一致するのは、125000、160000、142000の3件です。
COUNTIFS関数では、文字列条件と数値条件を自由に組み合わせられます。
条件範囲は複数指定できますが、各範囲の開始行と終了行は必ずそろえます。
担当別の達成件数、部署別の予算超過件数、商品別の販売数などを集計するときに活用できます。
【操作のポイント】文字列の条件は二重引用符で囲み、数値条件は比較演算子と数値を一つの条件として指定します。
商品区分と数値範囲を組み合わせる数式
商品区分がAで、売上が120000以上160000以下の件数を数えることもできます。
=COUNTIFS(B2:B6,”A”,C2:C6,”>=120000″,C2:C6,”<=160000″)
この数式では、商品区分と売上の下限、売上の上限という3つの条件を同時に確認しています。
サンプルでは田中さんの125000と160000が該当するため、結果は2です。
COUNTIFS関数は条件を追加するほど、目的のデータだけを絞り込めます。
ただし、条件が多い場合は基準セルを活用し、数式が読みやすい状態を保つことも重要です。
【操作のポイント】複数条件を入力した後は、条件範囲と条件が一組ずつ対応しているか確認します。
日付期間と金額条件を組み合わせる数式
売上管理では、特定の月の中で一定額以上になった件数を数えるケースもあります。
たとえばA列に日付、C列に売上があり、D2に開始日、E2に終了日、F2に基準金額を設定した場合は、次のように入力します。
=COUNTIFS(A2:A100,”>=”&D2,A2:A100,”<=”&E2,C2:C100,”>=”&F2)
日付の開始と終了、金額の下限をすべて満たす行だけがカウントされます。
日付条件は開始日以上かつ終了日以下の2条件に分けると、任意の期間を正確に指定できます。
月初と月末を基準セルに入力すれば、月ごとの実績確認にも応用できます。
【操作のポイント】期間集計では、終了日を含めるために「<=」を使い、日付列に時刻が含まれる場合は別途確認します。
COUNTIF・COUNTIFS関数のエラーと対処
続いては、COUNTIF関数とCOUNTIFS関数で結果が合わないときに確認したいポイントを解説していきます。
| 確認項目 | 主な原因 |
|---|---|
| 結果が0件 | 数値が文字列になっている |
| 件数が多い | 境界値や空白の条件が不適切 |
| 数式エラー | 条件範囲の大きさが異なる |
比較演算子と二重引用符の入力ミス
COUNTIF関数の条件では、比較演算子と数値の入力方法が最もよくあるつまずきです。
「>=80」のように条件全体を二重引用符で囲む必要があるのに、引用符の位置を誤ると正しく判定できません。
セル参照では「>=”&D2のように、比較演算子を文字列として残してアンパサンドで接続します。
数式バーで数式を確認し、引用符が左右で対応しているかを見るだけでも多くのミスを発見できます。
数式をコピーした際に全角記号が混ざっていないかも確認すると安心です。
【操作のポイント】条件を直接入力するか、基準値セルを参照するかによって、二重引用符の位置が変わります。
条件範囲の行数が異なる場合
COUNTIFS関数では、条件範囲のサイズが一致していなければなりません。
たとえばA2:A10とC2:C11のように一方だけ1行多い範囲を指定すると、計算エラーになることがあります。
表の1行目がヘッダーなら、各条件範囲はすべて2行目から同じ最終行までを選択します。
複数列を使うCOUNTIFS関数では、数式を入力後に各条件範囲の開始セルと終了セルを見比べることが大切です。
テーブル機能を利用している場合は、列名で範囲を指定すると行追加にも対応しやすくなります。
【操作のポイント】条件範囲を選択するときは、ヘッダー行を含めるかどうかを全列で統一します。
ワイルドカードと文字列条件の注意点
COUNTIF関数では、文字列条件にアスタリスクや疑問符を使うと部分一致の検索ができます。
一方で、これらの記号を商品名などに実際の文字として含めたい場合は、意図しない一致が起こることがあります。
また、「A」と「Ａ」のような半角と全角の違い、末尾のスペースなども集計結果に影響する場合があります。
文字列条件で件数が合わないときは、対象セルを一つずつ確認し、不要な空白を削除することが有効です。
TRIM関数やCLEAN関数を使い、入力データを整える運用も検討しましょう。
【操作のポイント】文字列の条件で集計する前に、表記ゆれ、全角半角、余分なスペースがないかを確認します。
まとめ エクセルで以上・以下の件数をカウントする方法
Excelで以上・以下の件数をカウントするときは、条件が1つならCOUNTIF関数、条件が2つ以上ならCOUNTIFS関数を使うのが基本です。
80以上なら「>=80」、100以下なら「<=100」のように、比較演算子と基準値を条件として設定します。
基準値を別セルに置く場合は、「>=”&セル番地のように比較演算子とセル参照を連結しましょう。
一定範囲の数値を数える場合は、下限以上と上限以下の2条件をCOUNTIFS関数で指定します。
担当者、商品区分、日付期間、金額などを組み合わせれば、実務で必要な条件別集計にも対応できます。
数式が期待どおりに動かないときは、比較記号、二重引用符、セルの数値形式、条件範囲の行数を順に確認することが解決への近道です。
COUNTIF・COUNTIFS関数を使い分け、一覧表から必要な件数を効率よく把握していきましょう。
