※本記事には広告・アフィリエイトリンクが含まれます。
「営業部で、しかも1万円以上の取引は何件あるか」「今月の注文で、完了済みのものは何件か」。こんなふうに、条件を2つ以上組み合わせて件数を数えたいときに使うのが、ExcelのCOUNTIFS関数です。
この記事では、Microsoftの公式サポートページを中心に、Excelの解説サイトやパソコン教室の記事を読み比べて、COUNTIFS関数の書式、以上・以下や期間の指定のしかた、「または」で数えたいときの考え方、よくあるつまずきを整理しました。
COUNTIFS関数の基本
まずは、COUNTIFS関数がどんな関数なのか、書式とCOUNTIF関数との違いから見ていきましょう。ここを押さえておくと、あとの使い方がぐっと分かりやすくなります。
書式は「範囲と条件」のペア、COUNTIFとの違い
Microsoftの公式サポートでは、COUNTIFS関数は「複数の範囲のセルに条件を適用して、すべての条件が満たされた回数をカウント」する関数と説明されています。書式は次のとおりです。
=COUNTIFS(条件範囲1, 検索条件1, [条件範囲2, 検索条件2], …)
条件範囲1と検索条件1は必ず指定し、2つ目からは必要な分だけ追加します。公式サポートによると、範囲と条件のペアは最大127組まで指定できます。検索条件には、32のような数値、”>32″のような式、B4のようなセル参照、”Windows”のような文字列が使えると書かれていました。
Excel解説サイト「Be Cool Users」では、この引数を「この範囲が、これと同じで、さらになおかつ、この範囲が、これと同じものだけ数えてね」と言いかえて説明していました。範囲と条件をセットで並べていく、と考えると覚えやすいですね。
COUNTIF関数との大きな違いは、指定できる条件の数です。Microsoftの公式サポートでは、COUNTIF関数で指定できるのは1つの検索条件だけで、複数の検索条件を指定する場合はCOUNTIFSを使うよう案内されています。
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でも説明されていました。
数値の条件には、比較演算子を使います。「=」は等しい、「<>」は等しくない、「>」はより大きい、「<」はより小さい、「>=」は以上、「<=」は以下という意味です。パソコン教室パレハでは、比較演算子を使う場合は、演算子も含めてダブルクォーテーションで囲む必要があると説明されていました。
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歳未満」とセルに文字で入れてしまうと文字列になり、条件として使えないので、数値と表示形式を組み合わせる方法が紹介されていました。
数式をオートフィルでコピーするなら、範囲がずれないようにF4キーで絶対参照にしておくことも大切です。「C3:C16」が「$C$3:$C$16」のように固定されます。条件のセルのほうは、コピーしたときに1つずつ下にずれてほしい場合が多いので、固定しなくてよいと説明されていました。
日付の期間で数える
「今月の注文件数」や「今日から1週間の予約件数」のように、日付の期間で数えたいときにもCOUNTIFS関数が使えます。書き方のパターンと注意点を見ていきましょう。
日付で数える：期間指定、1か月分や今日基準、時刻付きデータ
期間の件数は、「開始日以上」と「終了日以下」の2つの条件を同じ日付の範囲にかけて数えます。Microsoftの公式サポートでは、2010年1月1日から2010年12月31日までの日付を数える例が紹介されていました。
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日でも同じ考え方で集計できます。今日を基準にするなら、TODAY関数と組み合わせて「”<=”&TODAY()」や「”>=”&TODAY()」と書きます。Office Hackの記事では、翌日にファイルを開き直すとTODAY関数の日付が更新され、条件の期間も自動で変わったと紹介されていました。締め切りの管理や期限切れのチェックに使えそうですね。
日付に時刻が入っているデータでは、注意が必要です。へるぱそねっとの記事によると、終了日以下で数えると、終了日当日の時刻付きデータを取りこぼすことがあります。そこで「”<“&終了日+1」のように、翌日未満の形で書くほうが安全だと説明されていました。こうすると、終了日当日の23時59分59秒までのデータを含めやすくなるそうです。記事のサンプルでは、「<=終了日」と「<終了日+1」の2つの式を並べて、時刻付きのデータで結果がどう変わるかを比べられるようになっていました。
ほかにも、よくあるミスとして、開始日と終了日の2つの条件が必要なのにCOUNTIFを1つだけで済ませようとすること、比較演算子とセル参照を「&」でつないでいないこと、見た目は日付でも文字列になっていることが挙げられていました。結果がおかしいときは、日付が正しく日付の形式になっているかを確かめてみましょう。
うまく数えられないときのチェック
最後に、「または」の条件で数えたいときの考え方と、結果がおかしいときや0になってしまうときに確かめたい点をまとめます。
「または」の数え方、範囲の大きさ、ワイルドカードと入力ゆれ
COUNTIFS関数で指定した条件は、すべてを満たすもの、つまり「かつ」の条件として数えられます。パソコン教室パレハでは、部署名で「総務部」または「人事部」を数えたいときに、同じ範囲に2つの条件を並べると「総務部かつ人事部」になって0になってしまうと説明されていました。この場合は、次のように2つを足し算します。
=COUNTIFS(E3:E16,”総務部”)+COUNTIFS(E3:E16,”人事部”)
「営業部または東京都出身」のように、両方に当てはまる人がいる条件では、単純に足すと重なった人を2回数えてしまいます。そこで、「営業部」の件数と「東京都出身」の件数を足してから、「営業部かつ東京都出身」の件数を引く、という書き方が紹介されていました。Be Cool Usersでは、どれかを満たす場合を数えたいときには、AND条件とOR条件のどちらでも集計できるDCOUNT関数を使う方法も挙げられていました。
Microsoftの公式サポートでは、追加する範囲は、条件範囲1と同じ数の行と列にする必要があると書かれています。たとえば、1つ目の範囲がB2:B11なら、2つ目もE2:E11のように同じ行数にそろえます。範囲同士は隣り合っている必要はありません。
公式サポートの解説によると、それぞれの範囲の条件は1つのセルずつ順番に当てはめられ、1行目のセルがすべての条件を満たしていれば1つ数え、次に2行目のセルを調べる、というように最後まで評価されていきます。範囲の大きさがずれていると、この行ごとの対応が取れなくなるわけですね。
また、条件の引数が空のセルを参照していると、空のセルは0とみなされるそうです。条件を入れるセルを用意する方法では、条件のセルが空のままになっていないかも確かめておきましょう。
COUNTIF関数の公式サポートでは、別のブックを参照する数式は、そのブックが閉じていると#VALUE!エラーになることがあり、参照先のブックを開く必要があると説明されていました。別のファイルの表を数えるときは、参照先も開いておくと安心です。
条件には、半角の「?」と「」をワイルドカードとして使えます。公式サポートによると、「?」は任意の1文字、「」は1文字以上の任意の文字列を表します。ワイルドカードではなく、ふつうの文字として「?」や「*」を数えたいときは、前にチルダ「~」を付けます。
COUNTIF関数の公式サポートでは、大文字と小文字は区別されないこと、データの先頭や末尾に余分なスペースがあったり、まっすぐな引用符と丸い引用符が混ざっていたりすると、思った結果にならないことがあると説明されていました。そんなときは、CLEAN関数やTRIM関数を試すようすすめられています。また、255文字より長い文字列を照合すると正しい結果が返らないことや、セルの背景色や文字の色で数えることはできないことも書かれていました。
まとめ
COUNTIFS関数は、「条件範囲」と「検索条件」のペアを並べて、すべての条件に当てはまる件数を数える関数です。ペアは最大127組まで指定でき、文字の条件は「”」で囲み、以上や以下は「>=」「<=」などの比較演算子で書きます。
期間で数えるときは、同じ日付の範囲に開始日以上と終了日以下の2つの条件をかけ、時刻付きのデータなら翌日未満で書くと取りこぼしを防げます。「または」で数えたいときは足し算と引き算を組み合わせ、範囲の行数をそろえることも忘れないようにしましょう。
