Excelで平均より上・下・等しい件数を正しくカウントするCOUNTIF/COUNTIFSの使い方と0件になる原因の解消法

Excelで「平均より上の件数」「平均より下の件数」「平均と等しい件数」「平均の範囲内の件数」をカウントしようとして、COUNTやCOUNTIFを使っても結果が思ったとおりにならない……という相談はとても多いです。本記事では、ありがちなつまずきの原因と、実務でそのまま使える具体的な数式パターンをまとめて解説します。

Excelで「平均より上/下/等しい」を数える典型シーン

平均と比較して件数を数えたいシーンは、実務で頻繁に登場します。

• テストの点数一覧から「平均より上（優秀な人）」の人数を数える
• 営業成績から「平均売上以上」の担当者数を集計する
• センサー値から「平均±一定範囲内」に収まっているデータ数を確認する
• 品質管理で「平均±1σ（標準偏差）」に入っているサンプル数をカウントする

ところが、次のようなトラブルがよく起きます。

• COUNT関数を使ったら「いつも総件数と同じ」になってしまう
• COUNTIFで条件を入れたのに「なぜか0件」になってしまう
• 平均と同じはずの値が「等しい」と判定されない

まずは、関数の役割と違いを整理し、その上で具体的な数式パターンを確認していきます。

COUNTとCOUNTIF/COUNTIFSの違いを正しく理解する

「COUNTで条件つき集計ができる」と思い込んでいるケースがとても多いです。まずはここを整理します。

関数	主な役割	条件指定
COUNT	指定範囲の「数値が入力されているセルの個数」を数える	不可（条件は一切つけられない）
COUNTA	指定範囲の「空でないセルの個数」を数える	不可
COUNTIF	1つの条件でセルの個数を数える	文字列として「>50」「<=&B60」のように指定する
COUNTIFS	複数条件でセルの個数を数える	複数の範囲＋条件をセットで指定する

ポイントは次の2つです。

• COUNTは「数えるだけ」で比較はしてくれない
• 条件つき件数を求めるには、必ずCOUNTIFかCOUNTIFSを使う

つまり、=COUNT(B2:B31)は「B2:B31に数値がいくつ入っているか」を返すだけで、「平均より上」かどうかは関係ありません。この段階でつまずいている場合は、素直にCOUNTIFやCOUNTIFSに切り替えましょう。

COUNTIF/COUNTIFSで平均と比較するときの“条件の書き方”

COUNTIF/COUNTIFSで平均値と比較する場合、もっとも多いミスが「条件の書き方」です。以下の2つの書き方の違いが非常に重要です。

書き方	例	意味	結果
正しい例	">"&B60	「セルB60の値より大きい」という条件	期待どおり動作
誤った例1	">B60"	文字列「>B60」と等しいセルを探そうとする	通常0件になる
誤った例2	"B60"	文字列「B60」と等しいセルを探そうとする	これもほぼ0件

比較演算子（>や<=）とセル参照（B60など）は、必ず&で結合した「文字列条件」にする必要があります。

例えば、「範囲B2:B31の中でセルB60より大きい数値の件数」を数える式は次のとおりです。

=COUNTIF(B2:B31, ">"&B60)

ここを間違えると、条件に引っかかるセルが一つもなくなり、結果が0件になってしまいます。

具体例：平均より上・下・等しい件数を数える基本パターン

以降では、次の前提で説明します。

• データ範囲：B2:B31
• 平均値を計算しているセル：B60（中身は=AVERAGE(B2:B31)）

しきい値セル（B60）と比較する場合の式

平均値をあらかじめセルに計算しておき、そのセルを基準に件数を数えるパターンです。

求めたい件数	意味	数式（B60を基準）
平均より上	「B60より大きい」値の件数	=COUNTIF(B2:B31, ">"&B60)
平均より下	「B60より小さい」値の件数	=COUNTIF(B2:B31, "<"&B60)
平均以上	「B60以上」値の件数	=COUNTIF(B2:B31, ">="&B60)
平均以下	「B60以下」値の件数	=COUNTIF(B2:B31, "<="&B60)

平均を別セルに用意しておくと、「平均の計算方法を変えたい」ときに式を直さなくてよいので、実務ではこのパターンが扱いやすいです。

平均を式の中で直接使う場合の式

「セルを増やしたくない」「1つのセルに完結させたい」という場合は、COUNTIFの条件の中でAVERAGE関数を直接使います。

• 平均より上：
  =COUNTIF(B2:B31, ">"&AVERAGE(B2:B31))
• 平均より下：
  =COUNTIF(B2:B31, "<"&AVERAGE(B2:B31))

どちらのパターンも正しいですが、

• 式をシンプルにしたいなら「平均を別セルに置く」
• セルを増やしたくないなら「AVERAGEを直接書く」

という考え方で使い分けると良いでしょう。

「平均と等しい」件数はなぜうまく数えられないのか

平均との比較で一番やっかいなのが「平均と等しい件数」です。次のように書いても、期待どおりに動かないことがよくあります。

=COUNTIF(B2:B31, "="&AVERAGE(B2:B31))

原因は、平均が小数を含むことが多く、内部的には非常に細かい桁まで値が持たれているからです。

• 表示上は「75.3」となっていても、内部的には「75.29999999997」のような値になっている
• 一方、個々のデータは「75.3」と計算された値で、こちらは「75.3000000001」のようなわずかな差がある

このような「浮動小数点誤差」があるため、見た目が同じでも実際には「厳密には等しくない」と判定されてしまい、COUNTIFで「等しい」を判定すると0件になることがあります。

許容差を使って「ほぼ等しい」を数える

そこで実務では、次のように「許容差」を設けて判定する方法がおすすめです。

例：ABS(値 - 平均) < 1E-10 で「ほぼ等しい」とみなす

件数を数える式：

=SUMPRODUCT(--(ABS(B2:B31-AVERAGE(B2:B31))<1E-10))

1E-10は「0.0000000001」という意味で、「値と平均の差がごくわずかなら同じとみなす」という考え方です。

表示桁をそろえて比較したいときはROUNDを使う

「小数第2位までを比較して同じかどうかを判定したい」といった場面も多いです。その場合は、ROUNDで丸めてから比較します。

例：小数第2位までで平均と等しい件数

=SUMPRODUCT(--(ROUND(B2:B31,2)=ROUND(AVERAGE(B2:B31),2)))

この式では、

• ROUND(B2:B31,2)…B2:B31のそれぞれの値を小数第2位まで四捨五入
• ROUND(AVERAGE(B2:B31),2)…平均値も小数第2位まで四捨五入
• 四捨五入した結果が等しいものだけをTRUEとし、その件数を数えている

「表示上の桁数で判定したい」場合は、このROUND方式が実務的です。

「平均の範囲内」の件数をCOUNTIFSで数える

平均を中心とした範囲内の件数を数えたい場合は、COUNTIFSで上下の条件を同時に指定します。

平均±固定値（例：±0.5）の範囲内

例：平均±0.5の範囲内にあるデータ件数を求めたい場合

=COUNTIFS(
  B2:B31, ">="&(AVERAGE(B2:B31)-0.5),
  B2:B31, "<="&(AVERAGE(B2:B31)+0.5)
)

考え方は次のとおりです。

• 下限：AVERAGE(B2:B31)-0.5
• 上限：AVERAGE(B2:B31)+0.5
• 範囲B2:B31について、「下限以上」かつ「上限以下」を満たす件数を数える

平均±標準偏差1σの範囲内

品質管理などでよく使うのが、「平均±標準偏差（1σ）」の範囲内にあるデータの割合です。

• 母集団の標準偏差：STDEV.P
• 標本の標準偏差：STDEV.S

標本として扱うことが多いので、ここではSTDEV.Sを使った例を示します。

=COUNTIFS(
  B2:B31, ">="&(AVERAGE(B2:B31)-STDEV.S(B2:B31)),
  B2:B31, "<="&(AVERAGE(B2:B31)+STDEV.S(B2:B31))
)

この式により、「平均−1σ以上かつ平均＋1σ以下」の件数をカウントできます。全体の件数で割れば、その範囲に収まる比率も簡単に求められます。

例：1σ以内に収まる割合（％）

=COUNTIFS(
  B2:B31, ">="&(AVERAGE(B2:B31)-STDEV.S(B2:B31)),
  B2:B31, "<="&(AVERAGE(B2:B31)+STDEV.S(B2:B31))
)/COUNT(B2:B31)

「0件になってしまう」ときのチェックリスト（原因と対処法）

ここからは、COUNTIF/COUNTIFSの結果がなぜか0になるときに確認すべきポイントを整理します。

1. 条件式の書き方が間違っていないか

最も多いのが、比較演算子とセル参照の結合ミスです。

• 正しい：">"&B60
• 誤り1：">B60"
• 誤り2："B60"

条件の典型的な書き方をまとめておきます。

目的	例（B60を基準）	条件の書き方
より大きい	平均より上	">"&B60
以上	平均以上	">="&B60
より小さい	平均より下	"<"&B60
以下	平均以下	"<="&B60

「>」「<」などの演算子は、必ずダブルクォーテーションで囲み、その後ろに&でセル参照を連結する、という形を体に覚えさせましょう。

2. 数値が「文字列」として保存されていないか

次に多いのが、見た目は数字なのに、内部的には「文字列」として扱われているケースです。こうなると、数値として比較できず、結果が0件になりがちです。

数値が文字列化しているときの典型的な症状：

• セルの表示が左寄せになっている
• セル左上に「緑の三角（エラーインジケータ）」が表示されている
• 先頭や末尾にスペースが混ざっている
• 全角数字（例：「１」「２」）になっている
• 区切り記号や単位が含まれている（例：「1,000円」「100%」）

数値かどうかを関数で判定するなら、

• =ISNUMBER(B2) … TRUEなら数値
• =ISTEXT(B2) … TRUEなら文字列

などを使うと判断しやすくなります。

文字列数値を正しい数値に変換する方法

代表的な変換方法を整理しておきます。

方法	手順	メリット/注意点
[区切り位置]を使う	1. 文字列になっている範囲を選択 2. [データ]タブ → [区切り位置]をクリック 3. ウィザードは何も変えずにそのまま[完了]	手早く一括変換できる。余計な区切り設定をしないように注意。
1を乗算する	1. 空いているセルに「1」と入力 2. そのセルをコピー 3. 変換したい範囲を選択 4. [形式を選択して貼り付け] → [乗算]を選択	手軽で応用が利く。日付などに行うと意図しない変換になることがあるので注意。
VALUE関数を使う	1. 空き列に=VALUE(B2)を入力 2. 下までコピーして値を確定 3. 必要に応じて値貼り付けで上書き	元データを壊したくないときに安全。関数計算の分だけ手間は増える。

3. 範囲や参照セルを取り違えていないか

意外と多いのが、単純な範囲ミスです。

• データはB列なのに=COUNTIF(C2:C31, ...)と書いてしまっている
• 平均はB60なのに、別のセル（B6など）を参照している
• シートをまたぐときに、別シートの範囲を正しく参照できていない

複雑なブックでは、次のような表を紙に書き出して整理するだけでミスがグッと減ります。

役割	セル/範囲	備考
データ範囲	B2:B31	テストの点数
平均値	B60	=AVERAGE(B2:B31)
平均より上の件数	C60	=COUNTIF(B2:B31, ">"&B60)

また、数式をコピーするときに「平均セルB60への参照を絶対参照（$B$60）にしていなかった」といったミスもよくあります。平均セルを複数の式から参照する場合は、$B$60と絶対参照にしておくと安心です。

4. 平均・しきい値セルが数値になっているか

しきい値として参照しているセル自体がエラーや文字列になっていると、COUNTIFは正しく比較できません。

• 平均セルが#DIV/0!や#VALUE!になっていないか
• 平均セルに「計算済みの値」ではなく「文字列」が入っていないか
• 表示形式を変えすぎて、実際どんな値になっているか分からなくなっていないか

表示形式に関係なく「実際の値」を確認したい場合は、一時的に標準形式に戻すか、

• 平均セルを別セルで参照して、そのセルに数値形式を適用して確認

といった方法で内部値をチェックすると安心です。

5. 小数桁/丸めの影響を受けていないか

境界の値（ちょうど平均に一致しているはずの値）を含めたいかどうかによって、条件を

• ">"か">="
• "<"か"<="

のどちらにするかを意識的に選ぶことが大切です。

また、表示桁数で見たときの境界と内部的な境界がズレることもあります。その場合は、

• 境界となる値をROUND(平均, 桁数)で丸める
• または、データ側を同じ桁数で丸めてから比較する

といった工夫が有効です。

Microsoft 365ならFILTERで「どの行が該当するか」も確認する

Microsoft 365やExcel 2021以降では、「スピル」と呼ばれる動的配列機能が使えます。これを利用すると、該当する行の一覧を目で確認しながら件数も数えられます。

平均より上のデータを抽出する

例：B2:B31の中で平均より上の値を別の列に一覧表示する

=FILTER(B2:B31, B2:B31>AVERAGE(B2:B31))

この式を入力すると、フィルタ条件を満たすすべての行の値が、下方向に一括で表示されます。

件数はROWSで数える

抽出された件数を知りたいだけなら、ROWS関数を組み合わせます。

=ROWS(FILTER(B2:B31, B2:B31>AVERAGE(B2:B31)))

これにより、

• どの行が「平均より上」なのかを目視で確認できる
• 件数も自動的に数えられる

という2つのメリットが得られます。COUNTIFだけで結果を見ていると、「本当に合っているのか？」が分からなくなりがちなので、検証用としてFILTER＋ROWSはとても便利です。

実務でよくあるデータ別サンプル

ここまでの内容を踏まえ、実務でありがちなケース別に式を整理してみます。

テストの点数一覧から平均より上の生徒数を数える

• 範囲：B2:B31 に点数
• 平均セル：B60 （=AVERAGE(B2:B31)）

平均より上の生徒数：

=COUNTIF(B2:B31, ">"&B60)

平均以上の生徒数：

=COUNTIF(B2:B31, ">="&B60)

営業売上から「平均以上」の担当者数を数える

• 担当者名：A列
• 売上金額：B列（B2:B31）

平均売上：

=AVERAGE(B2:B31)

平均以上の担当者数：

=COUNTIF(B2:B31, ">="&AVERAGE(B2:B31))

売上データが文字列化していると正しく判定できないので、特に「1,000,000」など桁区切り付きで入力されている場合は、数値形式かどうかを必ず確認しましょう。

センサー値から「平均±0.5」の範囲内の件数を数える

• センサー値：B2:B1001

平均±0.5の範囲内：

=COUNTIFS(
  B2:B1001, ">="&(AVERAGE(B2:B1001)-0.5),
  B2:B1001, "<="&(AVERAGE(B2:B1001)+0.5)
)

また、「平均±1σの範囲内の割合」を出す場合は、

=COUNTIFS(
  B2:B1001, ">="&(AVERAGE(B2:B1001)-STDEV.S(B2:B1001)),
  B2:B1001, "<="&(AVERAGE(B2:B1001)+STDEV.S(B2:B1001))
)/COUNT(B2:B1001)

としておくと、管理図などの判断材料としてすぐに使える数字になります。

COUNT/COUNTIF/SUMPRODUCT/FILTERの使い分けの目安

最後に、「どの関数をいつ使うのか」を整理しておきます。

関数	得意な用途	平均比較のときの役割
COUNT	単純な数値セルの件数	分母（全件数）として使う。条件判定には使わない。
COUNTIF	1条件での件数カウント	「平均より上/下/以上/以下」の件数を求める基本。
COUNTIFS	複数条件での件数カウント	「平均±範囲」「平均±σ」など、上下2つの条件を同時に満たす件数に最適。
SUMPRODUCT	柔軟な条件判定、配列計算	許容差やROUNDを使った「平均とほぼ等しい」件数など、COUNTIFでは書きにくい条件に向く。
FILTER + ROWS	条件に合う行の一覧＋件数	Microsoft 365で、結果を目で確認しながら件数も知りたいときに便利。

まとめ：平均と比較して件数を数えるときの考え方

Excelで「平均より上/下/等しい/範囲内」の件数が正しく数えられないとき、多くの場合は次のどれかに原因があります。

• COUNTを使ってしまっている（条件判定したいならCOUNTIF/COUNTIFSを使う）
• 条件式の書き方を間違えている（">"&B60 のように演算子とセル参照を文字列結合する）
• 数値が文字列として保存されている（左寄せ・全角数字・スペースなどに注意）
• 範囲や参照セルを取り違えている（列・行・シート・絶対参照を確認）
• 平均・境界値の小数誤差（「等しい」判定は許容差やROUNDの利用が安全）

実務的には、次のように使い分けるのがおすすめです。

• 「平均より上/下/以上/以下」…COUNTIFでシンプルに
• 「平均±いくら」や「平均±σ」…COUNTIFSで上下2条件をまとめて処理
• 「平均と等しい」を厳密に扱いたい…SUMPRODUCT＋ABSまたはROUNDで許容差を考慮
• 結果を目視で確認しながら検証したい…Microsoft 365ならFILTER＋ROWS

一度、

• データ範囲
• 平均（しきい値）セル
• 条件つき件数のセル

を「どのセルがどの役割か」を紙や別シートに整理してから式を組み立てると、ミスが大幅に減り、COUNTIFやCOUNTIFSで0件になる謎も解消しやすくなります。この記事の数式テンプレートを土台として、自分のファイルに合わせてアレンジしてみてください。