初心者でもわかるCOUNTIFS関数の使い方｜複数の条件に一致する数や空白以外のセルを数える

COUNTIFS関数で複数条件を指定できるのはわかるけれど、「空白」や「0」以外のセルを数える方法がわからないという方は多いのではないでしょうか。

「以上」「以下」「未満」を指定する方法もあわせて覚えておくと、作業時間の短縮になり、Excelを使うのが楽しくなりますよ。

今回はExcel関数の中でも、頻繁に利用する「COUNTIFS関数」の使い方について初心者でもわかるように解説しています。

うろ覚えで使い方を忘れてしまったという方は、いま一度確認をしておきましょう！

COUNTIFS関数でできること

Excelでよく利用する関数として「COUNTIF（カウント・イフ）関数」があります。COUNTIF関数とは、セルの数を数えるための関数で、エクセル上でデータを数える関数として使われます。

ただし、COUNTIF関数ではひとつの条件でしかデータを数えることができません。

「でも、ひとつの条件だけじゃ絞り込みができない…」

そこで役に立つのが「COUNTIFS（カウント・イフ・エス）関数」です。

「COUNTIF関数」は一つしか条件が指定できないのに対して、「COUNTIFS関数」は複数の条件を指定し、その条件と合致しているセルの数を数えられます。

具体的には…

赤と白と入力されたセルの数だけを数えたい
テストの点数が60点以上80点未満の生徒の数を数えたい
データの項目の中で「済」が含まれる文字列の数を数えたい

のような場合にCOUNTIFS関数は非常に有効です。

POINT

「COUNTIF関数」と「COUNTIFS関数」の違いは、「指定できる条件の数」。「COUNTIFS関数」は「COUNTIF”S”関数」と「複数形のS」がついている事から複数の条件が指定できると考えると、覚えやすいですね。

COUNTIFS関数は複数の条件や、特定の文字列を指定する「ワイルドカード」の利用もできるため、仕事で使えるようになると作業時間の大幅な短縮が可能になるのです。

ちなみに「ワイルドカード」とは、任意の文字列を指定する際に使われる特殊文字です。ワイルドカードを使うことで、不特定の文字列を指定し、検索できます。ワイルドカードを使うことで、部分一致指定が可能になるのです。

ワイルドカードについては詳しく後述します。

COUNTIF関数の基本的な使い方

COUNTIFS関数の使い方を知る前に、まずはCOUNTIF関数の使い方をお伝えします。二つの違いを知ることで、より深く理解しやすくなるでしょう。

COUNTIF関数とCOUNTIFS関数は「検索したい条件の個数」で使い分けします。

条件が一つだけの場合はCOUNTIF関数、複数の条件の場合はCOUNTIFS関数を使います。

「月給が20万円以上」の人数を求めたい場合は、以下のように求めます。求める条件が一つだけなので、COUNTIF関数を用います。

以上のように、特定の数値や文字列を基準としてデータの個数を数えたい場合に使えます。文字列は以下の通りです。

=COUNTIF（範囲, 検索条件）

指定するのは「範囲」と「検索条件」の2つのみです。

COUNTIFS関数の基本的な使い方

まずCOUNTIFS関数の基本的な使い方の概要を説明し、次に基本的な使い方を手順ごとに説明していきます。

まず、COUNTIFS関数の書式を見てみましょう。

COUNTIFS関数の書式　=COUNTIFS（条件範囲1,検索条件1,条件範囲2,検索条件2,‥）

条件範囲1（必須）　検索の対象とするセルやセルの範囲を指定する

検索条件1（必須）　条件範囲1の中から任意のセルを検索する条件を指定する

条件範囲2（省略可）　追加したい検索の対象とするセルやセルの範囲を指定する

検索条件2（省略可）　条件範囲2の中から任意のセルを検索する条件を指定する

条件範囲3、検索条件3以降（省略可）　1つめや２つめと同様に必要に応じて追加指定

文章で説明しても伝わりにくいので、以下の例を参考に見てみましょう。

今回は左から生徒の出席番号、名前、テストの点数、採点の実施・未実施を表にしています。

例として、「D3からD8のセル」の中で「60点以上80点以下」のセルが何個あるのかを数えました。

COUNTIFS関数は、検索条件範囲と検索条件を1つずつ指定します。

例のように、検索条件として「60点以上」と「80点以下」2つの条件指定がある場合には、以下の条件範囲と検索条件の指定が必要です。

検索条件範囲1と検索条件1で60点以上のセルを拾う
検索条件範囲2と検索条件2で80点未満のセルを拾う

なお、80点「未満」の場合は80点ちょうどは含まれず、80点「以下」の場合は80点ちょうどを含みます。

このように、COUNTIFS関数では検索条件を複数指定することで、COUNTIF関数よりも複雑なデータ集計が可能となるのです。

それでは次の項目から、ひとつずつ手順を追っていきましょう。

1）関数名を入力

データの数を入力したい、セルもしくは数式バーに「＝COUNTIFS」と入力し、関数名をクリックします。

以下の方法でも関数の入力が可能です。

数を表示させたいセルをクリックした後、関数の挿入ボタンをクリック
検索窓に「COUNTIFS」と入力
OKをクリック

2）条件範囲1を選択

1つめの条件範囲を選択します。

例を取り上げると、今回は60点以上80点未満の数字が入力されているセルを数えたいので、検索条件範囲1には「D3:D8,」と入力しましょう。

これは「D3セルからD8セルの範囲の中から探してね」という意味合いです。

検索条件範囲を指定する方法は3つあります。

数式バーに直接入力
セルに入力した関数の「（）内」に入力
黄色い線で囲っている枠内をドラッグで範囲選択する

関数の挿入ボタンを押した場合にはボックスが表示されていますので、「検索条件範囲1」の欄に範囲となるセルを入力します。

3）検索条件1を指定

次に1つめの検索条件を指定します。

条件範囲1のセルのうち、60点以上のデータの数を数えたいので「検索条件1」として「>=60,」と入力し指定しましょう。

これは「範囲内で60点以上のセルを探してね」という意味合いです。

「>=指定したい数字」は指定したい数字「以上」を意味しており、入力した数字を含みます。

入力した数字を含まない場合は「>指定したい数字」です。

関数の挿入ボタンを押して入力する場合はボックス内の「検索条件1」の欄に「”>=60”」と入力します。

これでひとまず、60点以上のデータの数が数えられるようになりました。

4）条件範囲2を選択

次は80点未満に絞るように、2つめの条件範囲指定と条件を入力していきます。

ここからの流れは、要領は2)3)と同じです。

条件範囲1と同じように、D3セルからD8セルの中で80点未満の数を数えたいので「検索条件範囲2」として「D3:D8,」と入力しましょう。

D3セルからD8セルをドラッグして範囲選択することでも、条件範囲の指定が可能です。

関数の挿入ボタンを押して入力するときは、ボックス内の「検索条件範囲2」の欄に「D3:D8」と入力します。

5）検索条件2を選択

2つめの検索条件は80点未満です。

条件範囲2のセルのうち、80点以下のデータの数を数えたいので「検索条件2」として書式は「”<=80”」と入力します。冒頭でも触れましたが、「未満」は80を含まず、「以下」は80を含みます。

「以下」を指定する際は「<=指定したい数字」と入力しましょう。「未満」の場合は「<指定したい数字」です。

この結果G3セルに、D3からD8セルの範囲で点数が60点以上80点以下という条件を満たすセルの合計数（=2）が表示されました。

COUNTIFS関数で空白以外のセルを数える

膨大なデータ量があると、空白や「0」以外のセル数をカウントしたいケースも出てきますよね。

これはCOUNTIFS関数の基本的な使い方さえ覚えてしまえば簡単に解決できます。

他にもワイルドカードを利用した方法もありますので、目次の最後でお伝えします。

それでは、空白と0以外のセルの数え方を見てみましょう。

空白と「0」以外を数える

空白以外のみを数えるパターンと、空白と0以外を数えるパターンご紹介しますので、わかりやすいようにするため、D9セルの点数欄に0の数字が入った新しい行を追加しました。

まずは空白以外のみを数える方法を解説します。

まず条件を満たしたセルの合計数を出したいセルに「=COUNTIFS」と入力し、D3からD9の範囲でセルの合計数を計算したいので「=COUNTIFS(D3:D9,」と入力、指定します。

ここで、検索条件1として「”<>”」と入力しましょう。「<>」は空白という意味合いです。

空白以外は「0」を含みますので、書式を入力したG3セルには「6」と答えが返ってきましたね。

次に空白と0以外の数字を数える方法です。

空白と0以外の数字を数えるには「COUNTA関数」を利用します。

COUNTA関数とは、シンプルに空白以外のセルを数える方法です。

COUNTIF関数は、1つの条件のみに当てはまるセルを数えられる関数になります。

書式は以下の通りです。

「=COUNTA(D3:D9)-COUNTIF(D3:D9,”0”)」

数式の意味としては、【空白のセルの数から「0」が入力されているセルの数が引いた残りのセル数を参照する】ということです。

COUNTIFS関数で複数列を指定する

上記の画像では「りんごと梨の列」で「10より大きい」の数字が入力されているセルを数えました。

このように条件範囲を1列以上空けたい場合、1列ごとにCOUNTIFS関数を利用して数えます。

書式は以下のように指定しましょう。

「=COUNTIFS(C4:C6,”>10”+COUNTIFS(E4:E6,”>10”)」

各列でCOUNTIFS関数を利用してセルの数を数え、それらを足しているだけですので、さほど難しく考える必要はありません。

ワイルドカードを利用して文字列を指定する

COUNTIFS関数では、検索条件にワイルドカードを利用して文字列を指定できます。

上記の画像ではE4～E10の中で「済」の文字列で終わるデータの数を数えました。

また、ワイルドカードを利用すると以下のような条件検索が可能です。

「済」の文字列からはじまるデータ
「済」の文字列で終わるデータ
「済」の文字列を含むデータ
「済」の後ろに1文字を含むデータ

組み合わせが多数あるので、まずはワイルドカードの基本的な使い方を覚えましょう。

ワイルドカードとは

ワイルドカードは、特定の記号を検索条件に入力した文字の前後に付けることで、文字列を指定できる仕組みになります。

上記の画像は検索条件1に入力した「済」の後ろに「*（アスタリスク）」をつけることで、「済」の文字からはじまるデータの数をカウントしています。

「済」の文字からはじまるデータはないため「0」とカウントされていますね。

利用できるワイルドカードは以下です。

「*（アスタリスク）」

「”*済*”」…「済」を含む　使用例)採点済　採点済み　済

「”*済”」…「済」で終わる　使用例)採点済

「”済*”」…「済」からはじまる　使用例)済　済み

「?（クエスチョン）」

「”?済?”」…「済」の前後に1文字ずつ文字がある　使用例)経済的

「”?済”」…「済」の前に1文字ある　使用例)経済

「”済?”」…「済」の後ろに1文字ある　使用例)済み

番外編「(~ (チルダ) 」

*（アスタリスク）や?（クエスチョン）を文字としてカウントしたい場合に使用します。

COUNTIFS関数でワイルドカードを利用する例

最後にワイルドカードをCOUNTIFS関数で利用する例を3つご紹介しましょう。

例1.「=COUNTIFS(E4:E10,”*済*”)」

検索条件を「”*済*”」とすることによって、範囲内の「済」の文字列を含んだセルを数えられます。

この例の場合は、E4,5,6,7,8セルが「”*済*”」という検索条件に沿っているため、G4セルに合計数5が表示されています。

仮に「採点済み」のように「済」の後ろに「み」という送り仮名があったとしてもカウントされるので汎用性が高いワイルドカードです。

例2.「=COUNTIFS(E4:E10,”未?”)」

「未」の後ろに1文字あるデータを数えました。検索条件は「”未?”」となります。

クエスチョンは「1文字」しか数えません。

この例の場合は、「未実施」は「未」の後ろに文字がついているものの「未」の後ろは「実施」の2文字となっているため、「”未?”」という検索条件に沿っているセルは存在せず、この場合のカウントは0になり、G4セルに合計数0が表示されています。

例3.「=COUNTIFS(E4:E10,”?実?”)」

次の例は「実」の前後に1文字あるセルを数えています。

「実」の前後に1文字あるセルを数えるには「”?実?”」と入力しましょう。

こちらも、前後に2文字以上あった場合にはカウントはされません。

この例の場合は、E9セルとE10セルが「”?実?”」という検索条件に沿っているため、G4セルに合計数2が表示されています。

COUNTIFS関数でエラーを起こす際の対処法

COUNTIFS関数でエラーを起こして集計ができない、そんな時に考えられるエラーの原因を紹介します。

別ブックを選択している

#VALUE!エラーになる場合は、閉じた別ブックの範囲を指定している可能性があります。別ブックを開いていないか、他のブックを選択していないかをチェックしましょう。

条件の範囲が間違っている

条件範囲と検索条件が正しく指定できていますか？条件範囲の行と列が一致していないと、#VALUE!エラーを起こします。指定範囲が間違っていないかしっかりと確認しましょう。

255文字以上の文字列を指定している

指定する文字列は255文字以上では、指定できません。255文字以上を選択した場合は#VALUE!エラーになります。255文字以上の文字列を指定する場合には、CONCATENATE関数または文字列結合「&」を使ってくださいね。

VBAでCOUNTIFS関数を使用する方法

そもそもVBAとは、Visual Basic for Applicationsの略で、マイクロソフトのオフィスソフトに含まれる機能です。簡単に言うと、エクセルやワードなどのオフィスソフトで利用できる便利な機能のこと。そのVBAでもCountIfsを使用することで、複数の条件を満たすセルの個数を求められます。

条件式は以下の通りです。

【条件式】

単一条件：WorksheetFunction.CountIfs( セル範囲1, 条件1)

複数条件：WorksheetFunction.CountIfs(条件範囲1,検索条件1,条件範囲2,検索条件2)

まとめ

いかがだったでしょうか。今回は「COUNTIFS関数」の基本から応用まで解説してきました。Excel関数の中でも、頻繁に利用する関数なので使えるようになっておくと様々な場面で役立ちます。

データが少ない場合はそのまま数えてもいいのかもしれませんが、COUNTIFS関数を用いることによって数秒の短縮が積み重なって、年単位でみると数時間の短縮になり得ます。

まずは意識的に使用して、自分のものにしていきましょう。