※本記事には広告・アフィリエイトリンクが含まれます。
「営業部で、しかも1万円以上の取引は何件あるか」「今月の注文で、完了済みのものは何件か」。こんなふうに、条件を2つ以上組み合わせて件数を数えたいときに使うのが、ExcelのCOUNTIFS関数です。
この記事では、Microsoftの公式サポートページを中心に、Excelの解説サイトやパソコン教室の記事を読み比べて、COUNTIFS関数の書式、以上・以下や期間の指定のしかた、「または」で数えたいときの考え方、よくあるつまずきを整理しました。
COUNTIFS関数の基本
まずは、COUNTIFS関数がどんな関数なのか、書式とCOUNTIF関数との違いから見ていきましょう。ここを押さえておくと、あとの使い方がぐっと分かりやすくなります。
書式は「条件範囲と検索条件」のペア
Microsoftの公式サポートでは、COUNTIFS関数は「複数の範囲のセルに条件を適用して、すべての条件が満たされた回数をカウント」する関数と説明されています。書式は次のとおりです。
=COUNTIFS(条件範囲1, 検索条件1, [条件範囲2, 検索条件2], …)
条件範囲1と検索条件1は必ず指定し、2つ目からは必要な分だけ追加します。公式サポートによると、範囲と条件のペアは最大127組まで指定できます。検索条件には、32のような数値、”>32″のような式、B4のようなセル参照、”Windows”のような文字列が使えると書かれていました。
公式サポートの引数の説明を、もう少し詳しく見ておきます。条件範囲1は必須で、「対応する条件による評価の対象となる最初の範囲」です。検索条件1も必須で、計算の対象となるセルを定義する条件を指定します。条件範囲2と検索条件2から先はオプションで、使わなければ省略できます。
公式サポートの使用例1は、営業担当者ごとに、Q1・Q2・Q3のノルマを達成したかどうかを「○」で記録した表です。「=COUNTIFS(B2:B5,”=○”,C2:C5,”=○”)」は、Q1とQ2の両方のノルマを達成した販売員の人数を数える式で、答えは2人でした。範囲は縦の列だけでなく、「B2:D2」のように1人分の横の行を指定することもできるそうです。
使用例2では、「=COUNTIFS(A2:A7,”<6″,A2:A7,”>1″)」で、1から6の間の数（1と6は含まない）が入ったセルの数を数えています。「より小さい」「より大きい」の記号を使うと、境目の数は数に入らないことが分かります。
Excel解説サイト「Be Cool Users」では、この引数を「この範囲が、これと同じで、さらになおかつ、この範囲が、これと同じものだけ数えてね」と言いかえて説明していました。範囲と条件をセットで並べていく、と考えると覚えやすいですね。
COUNTIFとの違いと、「S」の覚え方
COUNTIF関数との大きな違いは、指定できる条件の数です。Microsoftの公式サポートでは、COUNTIF関数で指定できるのは1つの検索条件だけで、複数の検索条件を指定する場合はCOUNTIFSを使うよう案内されています。
COUNTIF関数の公式サポートでは、いちばん単純な形を、「どこを参照するか」と「何を探すか」の2つを指定するもの、と言いかえて紹介しています。A2〜A5に果物の名前が入った表で「=COUNTIF(A2:A5,”リンゴ”)」と書けば、「リンゴ」と入力されたセルの数（この例では2）が返る、という使い方です。
同じページには、「=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3)」のように、COUNTIFを2回使って「リンゴ」と「オレンジ」の数を合わせる例も載っていました。1つの式につき基準は1つなので、条件を増やしたいときはCOUNTIFを足すか、COUNTIFS関数を使えばよいという説明です。検索条件に書ける形はCOUNTIFS関数と同じで、公式サポートでは数値の32、比較演算子を使った”>32″、セル参照のB4、文字列の”リンゴ”が例に挙がっていました。
Be Cool Usersでは、条件が1つのときに使うCOUNTIFの後ろに、複数形の「S」が付いていると覚えるのがおすすめと書かれていました。合計のSUMIFとSUMIFS、平均のAVERAGEIFとAVERAGEIFSも、同じ関係になっています。
パソコン教室パレハの解説では、COUNTIFS関数は条件が1つでも使えるため、COUNTIF関数でできることはCOUNTIFS関数でもできると紹介されていました。どちらか1つを覚えるならCOUNTIFS、という考え方もあるそうです。Xでも、COUNTIFSを「条件の列, 条件」のペアを増やせば条件も増やせる関数として紹介する投稿や、IF・SUMIFSと並んで仕事で役立つ関数として挙げる投稿が見られました。
文字や数値の条件で数える
ここからは、実際の書き方を見ていきます。文字の条件、数値の以上・以下、セルに入れた条件を使う方法の順に紹介します。
文字の条件と、以上・以下の書き方
たとえば、B列に部署名、E列に進み具合が入った表で、「東エリア」で「完了」の件数を数えるなら、次のように書きます。Xで紹介されていた例と同じ形です。
=COUNTIFS(B2:B11,”東”,E2:E11,”完了”)
文字の条件は、半角のダブルクォーテーション「”」で囲みます。パソコン教室パレハの解説では、手で入力するときは、引数を半角カンマで区切ることと、文字列をダブルクォーテーションで囲むことの2つを覚えておこうと書かれていました。これができていないとエラーになるそうです。
入力に慣れていないうちは、「=COUNTIFS(」まで入力してから数式バーの左にある「fx」ボタンを押し、関数の引数のダイアログボックスで入力する方法もすすめられていました。ダイアログボックスでは、文字を入力して次の欄に移ると、自動でダブルクォーテーションが付くそうです。最初は条件範囲と検索条件の2つの欄しか見えませんが、入力していくと欄が増えていくとBe Cool Usersでも説明されていました。
数値の条件には、比較演算子を使います。「=」は等しい、「<>」は等しくない、「>」はより大きい、「<」はより小さい、「>=」は以上、「<=」は以下という意味です。パソコン教室パレハでは、比較演算子を使う場合は、演算子も含めてダブルクォーテーションで囲む必要があると説明されていました。COUNTIF関数の公式サポートでも、「=COUNTIF(B2:B5,”>55″)」で55より大きい値のセルを数える例（結果は2）が、同じ書き方で紹介されています。
Microsoftの公式サポートには、販売契約高が9000以上22500以下のセルを数える、次のような例が載っています。
=COUNTIFS(B2:B7,”>=9000″,B2:B7,”<=22500″)
ポイントは、同じ範囲B2:B7を2回指定して、「9000以上」と「22500以下」の2つの条件を両方かけていることです。範囲を指定するときは、このように同じ列を2回使うのがよくある書き方です。
パソコン教室パレハでは、30代の女性を数える例として、年齢の範囲に「>=30」と「<40」、性別の範囲に「女」の3つの条件を指定する方法が紹介されていました。「30代」を正しく数えるには、「30歳以上」と「40歳未満」の2つが必要になるわけです。
条件をセルに入れる方法と絶対参照
条件を数式に直接書くのではなく、別のセルに入れておくと、セルの中身を変えるだけで結果を変えられます。Be Cool Usersでは、F2に「男」、G2に「45」と入れておき、性別と年齢の範囲にそれぞれのセルを条件として指定する例が紹介されていました。
比較演算子とセルを組み合わせるときは、書き方に注意が必要です。パソコン教室パレハによると、比較演算子だけをダブルクォーテーションで囲み、「&」でセル番地とつなぎます。たとえば、H3に30と入れて30未満を数えるなら「”<“&H3」と書きます。「30歳未満」とセルに文字で入れてしまうと文字列になり、条件として使えないので、数値と表示形式を組み合わせる方法が紹介されていました。
Microsoftの公式サポートの使用例2にも、「=COUNTIFS(A2:A7,”<“&A6,B2:B7,”<“&B4)」という式があります。これは、条件の数値や日付を数式に直接書く代わりに、A6やB4のセルを参照した形です。COUNTIF関数の公式サポートでは、「=COUNTIF(B2:B5,”<>”&B4)」でB4の値（例では75）以外のセルを数える例が紹介されています。アンパサンド「&」が「<>」とB4の値をつなぎ、「=COUNTIF(B2:B5,”<>75”)」と同じ意味になる、という仕組みです。
よく使う範囲には、名前を付けておくのも1つの手です。公式サポートでは、式をわかりやすくするために、名前付き範囲を使う方法がすすめられています。COUNTIF関数の公式サポートによると、「=COUNTIF(fruit,”>=32″)-COUNTIF(fruit,”>85″)」のように、範囲の代わりに名前付き範囲を使うことができます。名前付き範囲は、今のワークシート、同じブックの別のワークシート、別のブックのどこで定義したものでも使えるそうです。
数式をオートフィルでコピーするなら、範囲がずれないようにF4キーで絶対参照にしておくことも大切です。「C3:C16」が「$C$3:$C$16」のように固定されます。条件のセルのほうは、コピーしたときに1つずつ下にずれてほしい場合が多いので、固定しなくてよいと説明されていました。
日付の期間で数える
「今月の注文件数」や「今日から1週間の予約件数」のように、日付の期間で数えたいときにもCOUNTIFS関数が使えます。書き方のパターンと注意点を見ていきましょう。
期間の指定と、1か月分の数え方
期間の件数は、「開始日以上」と「終了日以下」の2つの条件を同じ日付の範囲にかけて数えます。Microsoftの公式サポートでは、2010年1月1日から2010年12月31日までの日付を数える例が紹介されていました。式は「=COUNTIFS(B14:B17,”>=1/1/2010″,B14:B17,”<=12/31/2010″)」で、4つの日付のうち3つが数えられる、という例でした。
Office Hackの記事では、開始日をE2、終了日をE4に入れておき、次のように書く方法が紹介されています。
=COUNTIFS(A2:A16,”>=”&E2,A2:A16,”<=”&E4)
数式の中に日付を直接書くなら「”>=2023/9/27″」のように書きます。この場合は日付を必ずダブルクォーテーションで囲み、囲んでいないと数式が機能しないと説明されていました。開始日と終了日をセルに入れておく方法なら、セルを書き換えるだけで期間を変えられるので、日報や月報、キャンペーン期間の集計などに広く使えると「へるぱそねっと」の記事でも紹介されていました。
1か月分を数えるときは、「月初以上かつ翌月初未満」と書くと扱いやすいそうです。へるぱそねっとの記事では、G3に年、G4に月を入れて、次のように書く例が載っていました。
=COUNTIFS(B4:B11,”>=”&DATE(G3,G4,1),B4:B11,”<“&DATE(G3,G4+1,1))
この書き方なら、月の最後の日が28日でも31日でも同じ考え方で集計できます。ここで気になるのが、12月の集計です。G4に12を入れると、「G4+1」は13になります。Microsoftの公式サポートによると、DATE関数の月に12より大きい数を指定すると、その年の1月から月数を足した日付になります。たとえば「DATE(2008,14,2)」は2009年2月2日です。そのため、12月の集計でも「DATE(年,13,1)」が翌年の1月1日になり、同じ式のまま使えます。
日付を「以上」「以下」で比べられるのは、Excelが日付をシリアル値という連続した数値で保存しているからです。公式サポートでは、1900年1月1日がシリアル値1で、2008年1月1日は39448になると説明されています。日付の条件は、見た目の文字ではなく、この数値の大きさで比べられているわけです。
今日を基準にする方法と、時刻付きデータの注意
今日を基準にするなら、TODAY関数と組み合わせて「”<=”&TODAY()」や「”>=”&TODAY()」と書きます。Office Hackの記事では、翌日にファイルを開き直すとTODAY関数の日付が更新され、条件の期間も自動で変わったと紹介されていました。締め切りの管理や期限切れのチェックに使えそうですね。
TODAY関数は、引数のない関数です。Microsoftの公式サポートでは、現在の日付のシリアル値を返し、入力する前のセルの表示形式が「標準」なら「日付」に変わると説明されています。日付が思ったとおりに更新されないときは、［ファイル］タブの［オプション］から［数式］の［計算方法の設定］を開き、［自動］が選ばれているか確かめるよう案内されていました。シリアル値そのものを見たいときは、セルの書式を「標準」か「数値」に変えます。
日付に時刻が入っているデータでは、注意が必要です。へるぱそねっとの記事によると、終了日以下で数えると、終了日当日の時刻付きデータを取りこぼすことがあります。そこで「”<“&終了日+1」のように、翌日未満の形で書くほうが安全だと説明されていました。こうすると、終了日当日の23時59分59秒までのデータを含めやすくなるそうです。記事のサンプルでは、「<=終了日」と「<終了日+1」の2つの式を並べて、時刻付きのデータで結果がどう変わるかを比べられるようになっていました。公式サポートのTIME関数のページによると、時刻は日付を構成する値として小数で表され、0から0.99988426までの値が0時0分0秒から23時59分59秒にあたります。終了日の10時のデータは「終了日のシリアル値＋小数」になるので、「<=終了日」の条件では外れてしまうわけです。
ほかにも、よくあるミスとして、開始日と終了日の2つの条件が必要なのにCOUNTIFを1つだけで済ませようとすること、比較演算子とセル参照を「&」でつないでいないこと、見た目は日付でも文字列になっていることが挙げられていました。結果がおかしいときは、日付が正しく日付の形式になっているかを確かめてみましょう。
うまく数えられないときのチェック
最後に、「または」の条件で数えたいときの考え方と、結果がおかしいときや0になってしまうときに確かめたい点をまとめます。
「または」は足し算と引き算で数える
COUNTIFS関数で指定した条件は、すべてを満たすもの、つまり「かつ」の条件として数えられます。パソコン教室パレハでは、部署名で「総務部」または「人事部」を数えたいときに、同じ範囲に2つの条件を並べると「総務部かつ人事部」になって0になってしまうと説明されていました。この場合は、次のように2つを足し算します。
=COUNTIFS(E3:E16,”総務部”)+COUNTIFS(E3:E16,”人事部”)
「営業部または東京都出身」のように、両方に当てはまる人がいる条件では、単純に足すと重なった人を2回数えてしまいます。そこで、「営業部」の件数と「東京都出身」の件数を足してから、「営業部かつ東京都出身」の件数を引く、という書き方が紹介されていました。Be Cool Usersでは、どれかを満たす場合を数えたいときには、AND条件とOR条件のどちらでも集計できるDCOUNT関数を使う方法も挙げられていました。
DCOUNT関数の公式サポートでは、書式は「DCOUNT(データベース, フィールド, 検索条件)」で、条件を満たすレコードの中で数値が入ったセルの個数を返すと説明されています。データベースにする表の先頭の行には、各列の見出しが必要です。検索条件には、列見出しと条件を入れたセルを少なくとも1つずつ含む範囲を指定し、その範囲が表と重ならないようにすることも書かれていました。検索条件の範囲は、表の最終行のすぐ下には置かないよう注意が必要です。表にデータを追加すると、その行に追加されるためだそうです。
数値の範囲を数える方法としては、SUMPRODUCT関数も公式サポートで紹介されています。「=SUMPRODUCT((B2:B7>=9000)(B2:B7<=22500))」は、COUNTIFSの「9000以上22500以下」の例と同じく、4件という結果になりました。条件どうしを「」でかけ合わせると「かつ」になる書き方です。
範囲の大きさ、ワイルドカードと入力ゆれ
Microsoftの公式サポートでは、追加する範囲は、条件範囲1と同じ数の行と列にする必要があると書かれています。たとえば、1つ目の範囲がB2:B11なら、2つ目もE2:E11のように同じ行数にそろえます。範囲同士は隣り合っている必要はありません。
公式サポートの解説によると、それぞれの範囲の条件は1つのセルずつ順番に当てはめられ、1行目のセルがすべての条件を満たしていれば1つ数え、次に2行目のセルを調べる、というように最後まで評価されていきます。範囲の大きさがずれていると、この行ごとの対応が取れなくなるわけですね。
また、条件の引数が空のセルを参照していると、空のセルは0とみなされるそうです。条件を入れるセルを用意する方法では、条件のセルが空のままになっていないかも確かめておきましょう。
COUNTIF関数の公式サポートでは、別のブックを参照する数式は、そのブックが閉じていると#VALUE!エラーになることがあり、参照先のブックを開く必要があると説明されていました。別のファイルの表を数えるときは、参照先も開いておくと安心です。
条件には、半角の「?」と「」をワイルドカードとして使えます。公式サポートによると、「?」は任意の1文字、「」は1文字以上の任意の文字列を表します。ワイルドカードではなく、ふつうの文字として「?」や「」を数えたいときは、前にチルダ「~」を付けます。COUNTIF関数の公式サポートには、「=COUNTIF(A2:A5,”“)」で何かしらの文字が入ったセルを数える例や、「?」を6つ並べたあとに「ゴ」を付けた条件で、「ゴ」で終わるちょうど7文字のセルを数える例が載っていました。
COUNTIF関数の公式サポートでは、大文字と小文字は区別されないこと、データの先頭や末尾に余分なスペースがあったり、まっすぐな引用符と丸い引用符が混ざっていたりすると、思った結果にならないことがあると説明されていました。そんなときは、CLEAN関数やTRIM関数を試すようすすめられています。また、255文字より長い文字列を照合すると正しい結果が返らないことや、セルの背景色や文字の色で数えることはできないことも書かれていました。255文字より長い文字列を照合したいときは、CONCATENATE関数か連結演算子「&」を使う方法が案内されています。色で数えたい場合は、VBAで作るユーザー定義関数を使えば数えられるとのことでした。
まとめ
COUNTIFS関数は、「条件範囲」と「検索条件」のペアを並べて、すべての条件に当てはまる件数を数える関数です。ペアは最大127組まで指定でき、文字の条件は「”」で囲み、以上や以下は「>=」「<=」などの比較演算子で書きます。
期間で数えるときは、同じ日付の範囲に開始日以上と終了日以下の2つの条件をかけ、時刻付きのデータなら翌日未満で書くと取りこぼしを防げます。「または」で数えたいときは足し算と引き算を組み合わせ、範囲の行数をそろえることも忘れないようにしましょう。
