Office Hack

ExcelのCOUNTIF関数の使い方|条件に一致するデータの個数を求める

  • by yoshihiro
  • Release
  • Update

COUNTIF(カウントイフ)関数は指定した範囲の中で1つの検索条件に一致するセルがいくつあるかを求める関数です。

この関数を使えば特定の文字が入っているセルの個数を数えたり、逆に特定の文字以外のセルの個数を数えることができます。他によく使用する例としては、空白セル以外を数えたいときにCOUNTIF関数を使用します。

以下の記事では、COUNTIF関数の基本的な使い方をパターンごとにご紹介していきます。またCOUNTIF関数を「IF関数」や「SUMPRODUCT関数」と組合わせた方法も説明しています。

書式

条件に合ったセルの個数を求めます。

=COUNTIF(範囲, 検索条件)

引数名 説明
範囲 (必須) 検索の対象とするセルやセルの範囲を指定します。数値や配列、名前付き範囲、参照が指定できます。 
検索条件(必須)

「範囲」の中からセルを検索するための条件を、数値、式、セル範囲、または文字列で指定します。文字列を指定する場合は、「"(ダブルクォーテーション)」で囲みます。

COUNTIF関数で指定できるのは、単一の検索条件のみです。 複数の検索条件を指定する場合は、COUNTIFS関数を使用します。

COUNTIF関数で使える比較演算子

検索条件で式を指定する際に比較演算子を使用することができます。

演算子 読み方 説明
= 等号、イコール 等しい
<> 山カッコ、大なり小なり 等しくない
> 大なり(だいなり) より大きい
< 小なり(しょうなり) より小さい
>= 大なりイコール 以上
<= 小なりイコール 以下

COUNTIF関数の基本的な使い方

範囲 =COUNTIF(▷範囲◁, 検索条件)

元のデータ

COUNTIF関数の範囲を指定する方法は3つあります。

例のような表がある場合、COUNTIF関数を使って「リンゴ」が入っているセルの個数を求める範囲を指定してみましょう。

関数の引数ダイアログボックスで範囲を指定する
関数名の記入

まずF3セルを選択し、『=COUNTIF(』を入力します。次に【fx】ボタンを押します。

関数の引数ダイアログボックス

「関数の引数ダイアログボックス」で「範囲」の右端にある【↑】ボタンを押します。

範囲の指定

『B3:B6』と入力し、右端の【↓】ボタンを押します。始点と終点の間は「:(コロン)」を入力します。

範囲を確定する

【OK】ボタンを押します。

ドラッグで範囲を指定する方法
ドラッグで範囲選択

F3セルを選択してから『=COUNTIF(』を入力し、B3セルからB6セルをマウスでドラッグすると範囲が入力されました。

名前付き範囲で範囲を指定する方法
名前の定義

B3セルからB6セルまでを選択し、【数式】タブ、【名前の定義】を押します。

名前を定義

名前に『フルーツ』を入力し、【OK】ボタンを押します。

名前の定義を使った範囲指定

F3セルを選択してから『=COUNTIF(フルーツ』と入力します。「フルーツ」と定義した範囲が選択されました。

検索条件 =COUNTIF(範囲, ▷検索条件◁)

検索条件には数値、式、セル範囲、または文字列で指定することが可能です。組み合わせることでさまざまな条件に合ったセルの個数を求めることができます。

よく使用される検索条件を中心にご紹介していきます。

特定の文字列のセルを数える
元のデータ

COUNTIF関数というとイメージするのが特定の文字列や数値が入っているセルの個数を求めたいときだと思います。例のような表がある場合、COUNTIF関数を使って「リンゴ」が入っているセルの個数を求めましょう。まずF3セルを選択します。

巣関数の入力

数式バーに『=COUNTIF(B3:B6,"リンゴ")』を入力します。その後、Enterを押します。

関数の結果

F3セルに選択範囲の中で「リンゴ」と入力されているセルの個数である「2」が表示されました。

文字列の注意点

特定の文字列を指定する場合、指定した文字列と完全一致しているセルをカウントします。平仮名の「りんご」のように異なる表記にするとヒットすることができませんので注意してください。

別のセルを参照して目的のセルを数える
関数の入力

F3セルを選択し、数式バーに『=COUNTIF(B3:B6,E3)』を入力します。その後、Enterを押します。

関数の結果

E3セルに入力されている「リンゴ」を検索条件としてセルの個数が「2」と表示されました。

COUNTIF関数を使用して複数の条件に合ったセルの個数を求める
複合条件

複数の条件を検索したいとき、COUNTIFS関数を使わずにCOUNTIF関数だけで求めることができます。

F4セルを選択し、数式バーに『=COUNTIF(B3:B6,B3)+COUNTIF(B3:B6,B5)』を入力します。その後、Enterを押します。

関数の結果

選択範囲の中でB3セルの値(リンゴ)とB5セルの値(モモ)が入力されているセルの個数として「3」が表示されました。

この数式ではCOUNTIFを2回使用しました。このようにCOUNTIFを複数組み合わせることで複数の検索条件に対応することができます。

指定した数値よりも大きい、または小さい数値の個数を数える
大なり

F5セルを選択し、数式バーに『=COUNTIF(C3:C6,">55")』を入力します。その後、Enterを押します。

関数の結果

55より大きい値が入力されているセルの個数は「2」になりました。反対に小さい値を検索したい場合は比較演算子の「<」(小なり)を使用します。

「○○以上/○○以下」である数値セルを数える
大なりイコール

F6セルを選択し、数式バーに『=COUNTIF(C3:C6,">=32")』を入力します。その後、Enterを押します。

関数の結果

32以上の値が入力されているセルの個数は「4」になりました。反対に特定の数値以下のセルを検索したい場合は比較演算子の「<=」(小なりイコール)を使用します。

以上、以下の条件

「○以上、○以下」の両方に合致するセルの数を求める時は、引き算を利用します。F7セルを選択し、数式バーに『=COUNTIF(C3:C6,">=32")-COUNTIF(C3:C6,">85")』を入力します。その後、Enterを押します。

関数の結果

32以上85以下の値が入力されているセルの個数が「3」と求められました。32以上のセルの個数は4、85より大きいセルの数値は1なので「4-1」で結果は3になります。

特定の文字以外を数える
特定の文字以外を検索

F8セルを選択し、数式バーに『=COUNTIF(B3:B6,"<>オレンジ")』を入力します。その後、Enterを押します。

関数の結果

「オレンジ」の値が入力されている以外のセルの個数として「3」が求められました。

セルの参照

検索条件で文字列以外にセルを参照することもできます。

ワイルドカード(アスタリスク)を使って「○○で始まる/○○で終わる/○○を含む」セルを数える

検索条件として決まった文字列や数字以外にもある特定の文字列を含んだセルの個数を求めたいときも多いと思います。

検索条件でワイルドカードを使用するとさらに複雑な検索もすることが可能です。ワイルドカードの「?」疑問符は任意の1文字を表し、「*」アスタリスクは 1文字以上の任意の文字列を表します。

ワイルドカードでの検索

F9セルを選択し、数式バーに『=COUNTIF(B3:B6,"*")』を入力します。その後、Enterを押します。

関数の結果

検索範囲で任意のテキストが入力されているセルの個数として「4」が求められました。

ワイルドカードでの検索

F10セルを選択し、数式バーに『=COUNTIF(B3:B6,"??ゴ")』を入力します。その後、Enterを押します。

関数の結果

検索範囲で3文字でかつ最後に「ゴ」が入力されているセルの個数として「2」が求められました。

ワイルドカードでの検索

F11セルを選択し、数式バーに『=COUNTIF(B3:B7,"リンゴ*")』を入力します。その後、Enterを押します。

関数の結果

検索範囲で先頭に「リンゴ」が入力されているセルの個数として「3」が求められました。

空白以外を数える
空白以外のセルを数える

空白以外のセルの個数を求めるには2つ方法があります。「COUNTA関数を使用する方法」と「COUNTIF関数で演算子で指定する方法」です

今回はCOUNTIF関数を使用する方法をご紹介します。F12セルを選択し、数式バーに『=COUNTIF(B3:B11,"<>")』を入力します。その後、Enterを押します。

関数の結果

空白以外の何かしらの値、文字が入っているセルをカウントした結果として「8」が表示されました。

日付が入力されたセルの個数(数)をカウントする
特定の日付を検索

F3セルを選択し、数式バーに『=COUNTIF(C3:C12,"2019/1/7")』を入力します。その後、Enterを押します。

関数の結果

検索範囲で「2019/1/7」が入力されているセルの個数として「3」が求められました。

以前の条件

F4セルを選択し、数式バーに『=COUNTIF(C3:C12,"<=2019/1/7")』を入力します。その後、Enterを押します。

関数の結果

検索範囲で「2019/1/7」以前が入力されているセルの個数として「7」が求められました。

※「以前」はその日も含んだそれより前のことを指します。

以降の条件

F5セルを選択し、数式バーに『=COUNTIF(C3:C12,">=2019/1/7")』を入力します。その後、Enterを押します。

関数の結果

検索範囲で「2019/1/7」以降が入力されているセルの個数として「6」が求められました。

※「以降」はその日も含んだそれより後のことを指します。

複数の条件の日付を検索

F6セルを選択し、数式バーに『=COUNTIF(C3:C12,"<=2019/1/31")-COUNTIF(C3:C12,"<=2019/1/1")』を入力します。その後、Enterを押します。

関数の結果

検索範囲で「2019/1/1」から「2019/1/31」までが入力されているセルの個数として「5」が求められました。

重複したセルで連番を振る、または同番を振る

セルに重複したデータが入っていた場合、その重複データごとに連番もしくは同番を振る方法についてご紹介します。

連番の数式

連番振りの場合から説明します。D3セルを選択し、『=COUNTIF($B$2:B3,B3)』を入力します。セル範囲の最初のところだけ絶対参照します。オートフィルをしたときに相対参照部分だけが変わり、自動的に参照範囲を変更して個数を数えます。

オートフィル

D3セルの右下を選択して下まで引っ張ります。

関数の結果

上からカウントしたときに2つ目の重複については「2」、3つ目は「3」の数字が表示されます。これで重複しているデータに連番を振ることができます。

関数の入力

次に同番振りの方法を説明します。D3セルに『1』を入力します。次にD4セルに『=IF(B4<>B3,D3+1,IF(B4=B3,D3,COUNTIF($B$4:B4,B4)))』を入力します。

オートフィルタ

下方へオートフィルコピーします。

関数の結果

同番が振られました。

COUNTIF関数と他の関数を組み合わせた使い方

重複をチェックする(IF×COUNTIF)

重複チェック

重複チェックをするには、IF関数で真の場合は、★を表示、偽の場合は、何も表示しないことにします。IF関数の論理式としてCOUNTIF関数を使用します。

関数の入力

『IF(COUNTIF($B$2:B10,B3)>1,"★","")』を入力します。$B$2:B10と複合参照にすることで、B列の同じ行までに該当するデータが何個あるかを求めます。「COUNTIF($B$2:B10,B3)>1」は1より大きい、つまり、B列の同じ行までに該当するデータが2以上のときに重複と表示するということになります。

オートフィルタ

オートフィルで下までコピーします。

関数の結果

重複チェックができました。

重複しているデータを1件としてカウントする(COUNTIF×SUMPRODUCT)

関数の入力

データの種類を調べてみましょう。F3セルを選択し、『=SUMPRODUCT(1/COUNTIF(B3:B10,B3:B10))』を入力します。その後、Enterを押します。

関数の結果

結果として「6」種類と表示されました。

VBAとCOUNTIFを組合わせた使い方

空白以外のセルをカウント

VBAの起動

B3セルからB10セルまでの範囲で空白以外のセルを数えます。【開発】タブ、【Visual Basic】を選択します。

シートの選択

該当のシートをダブルクリックします。

コードの記述

下記のコードを入力します。


Sub 空白以外のセルをカウント()
 Dim n As Long
 n = WorksheetFunction.CountIf(Range("B3:B10"), "<>")
 MsgBox n
End Sub

VBAの結果

アラートで「7」と結果が表示されました。

COUNTIF関数の注意点

フィルタをかけるとCOUNTIF関数で数えられない

関数の入力

COUNTIF関数はフィルターを考慮しないためフィルタしても結果が変わりません。実際に例を見てみましょう。E2セルを選択し、『=COUNTIF(B3:B10,"リンゴ")』を入力すると「2」と表示されました。

フィルタをかける

フィルタを選択し、【数値フィルター】、【指定の値より大きい】を選択します。

範囲の指定

売上個数に『50』を入力し、【OK】ボタンを押します。

関数の結果

フィルタをかけて「リンゴ」が減ったにもかかわらずCOUNTIFの結果は「2」のままになっています。

関数の入力

フィルタをかけてもCOUNTIF関数の結果が反映されるようにします。E2セルを選択し、『=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(INDIRECT("1:"&ROWS(B3:B10))),))*(B3:B10="リンゴ"))』を入力します。

関数の結果

フィルタが反映された状態でもフィルタで表示されている中でCOUNTIF関数の結果である「1」が表示されました。

テキスト文字列の大文字と小文字が区別されない

大文字小文字

COUNTIF関数では、テキスト文字列の大文字と小文字が区別されないことに注意してください。

大文字小文字

検索条件として「apple」と指定しても「APPLE」も検索対象になります。

長い文字列を照合すると、間違った値が返されてしまう

関数の入力

長い文字列を照合すると、間違った値が返されることがあります。「256」以上の文字列を検索対象とします。

関数の結果

「#VALUE!」が表示されエラーになりました。

関数の入力

255文字の文字列を検索対象とします。

関数の結果

こちらはきちんと検索されました。256文字以上は検索条件として指定しないようにしましょう。

他の関数も合わせてチェック!

Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。