先月、月次の営業会議で使う集計表を作っていたときのことです。「東京都・かつ契約成立」の件数を数式で出したところ46件。ところが念のため手作業で数え直すと47件で、1件だけ合いませんでした。件数としてはたった1件の差なのに、原因が分からないまま40分近く画面とにらめっこする羽目になりました。
結局、原因は単純で、条件範囲の片方をA2:A300、もう片方をB2:B299と指定していて、1行分だけズレていたことでした。条件が1つだけなら気づきやすいミスですが、条件が2つ、3つと増えるほど、こうした「範囲のズレ」に気づきにくくなります。
COUNTIFS関数は、複数条件で件数を数えたいときに欠かせない関数です。ただし条件が増える分、ズレの原因になり得る場所も増えます。今回は基本の書き方から、実務でよく使う型、そして数が合わなくなったときにどこから確認すればいいのかを、順を追って整理していきます。
こんな症状に心当たりはありませんか？
本題に入る前に、いま抱えている状況がどれに近いか確認してください。当てはまる項目によって、優先して見るべき章が変わります。
- 数式を入れた瞬間にエラー（#VALUE!など）が出る → 「3. 数式そのものが動かないケース」
- エラーは出ないのに、手計算と数式の結果が数件だけズレる → 「4. 数は出るのに件数が合わないケース」
- 条件を1つ増やしたら、急に0件やありえない件数になった → 「4-2. AND条件のつもりがOR条件を求めていた」
- 表自体は昨日まで合っていたのに、今日見たらズレていた → 「4-4. 日付や文字列の表記ゆれ」
心当たりがある章から先に読んでもらって構いません。
1. COUNTIFS関数の基本の書き方
COUNTIFS関数は、複数の条件をすべて満たすセルの個数を数える関数です。書式は次の通りです。
=COUNTIFS(条件範囲1, 検索条件1, 条件範囲2, 検索条件2, ...)「範囲と条件のペア」を必要な数だけ並べて指定します。COUNTIF関数に、範囲と条件のセットをもう1組足しただけ、とイメージすると分かりやすくなります。ペアの上限は127組ありますが、実務でそこまで条件を積み上げることはまずありません。
たとえばA列に部署名、B列に評価結果が入っている表で、「営業部」かつ「合格」の件数を数えたい場合は次のように書きます。
=COUNTIFS(A2:A200, "営業部", B2:B200, "合格")A列が「営業部」で、なおかつB列が「合格」になっている行だけが数えられます。COUNTIFS関数の条件は、すべて「AND（かつ）」の関係で判定される、という点がまず押さえておきたい基本ルールです。
2. 実務で使う4つの型
条件の組み合わせ方によって、COUNTIFSの使い方は大きく4つの型に分けられます。自分がやりたい集計がどの型に近いかを先に把握しておくと、数式を組み立てるときに迷いにくくなります。
型1：属性を掛け合わせて絞り込む
=COUNTIFS(A2:A300, "営業部", C2:C300, "2026/4")部署と対象月のように、属性を2つ以上掛け合わせて件数を絞り込む、もっとも基本的な型です。月の部分をセル参照にしておけば、参照先を書き換えるだけで集計対象の月を切り替えられます。
型2：期間で絞り込む
=COUNTIFS(D2:D200, ">="&DATE(2026,4,1), D2:D200, "<="&DATE(2026,4,30))同じ列に対して「以上」と「以下」の条件を2つ指定することで、期間を区切って件数を数えられます。同じD列を条件範囲として2回使っている点がポイントで、範囲は重複して指定してもまったく問題ありません。
型3：クロス集計を一気に埋める
=COUNTIFS($A$2:$A$500, $H2, $B$2:$B$500, I$1)行に年代、列に満足度を並べたクロス集計表を作る型です。条件範囲を絶対参照（$を付ける）にして、条件セル側だけを相対参照にしておくと、この数式を表全体にコピーするだけで、マス目ごとの集計が一気に完成します。先ほどの型1・型2が「1つの答え」を出すのに対して、この型3は「表全体を埋める」ためのものだと考えると使い分けやすくなります。
型4：OR条件を混ぜて数える
=SUM(COUNTIFS(E2:E100, {"未対応","対応中"}, F2:F100, "至急"))COUNTIFS関数自体はAND判定しかできませんが、条件の一部を波かっこで配列にしてまとめ、外側をSUM関数で囲むことで、「未対応または対応中、かつ至急」のような、AND条件とOR条件を組み合わせた集計が可能になります。この型だけ少し特殊なので、慣れないうちは「配列部分をSUMで合算している」という構造を意識しながら組み立ててください。
3. 数式そのものが動かないケース
条件を入れた瞬間に#VALUE!などのエラーが出る場合、まず疑うべきは次の2点です。
- 条件範囲の行数が、ペアごとに一致していない（例：条件範囲1はA2:A200、条件範囲2はB2:B199のように1行短い）
- 検索条件の書き方が誤っている（比較演算子を含む場合に
">="&セル番地のように文字列連結を忘れている）
特に行数のズレは、COUNTIFS関数でもっとも起きやすいトラブルです。条件範囲が複数になる分、SUMIFSやCOUNTIF単体よりも、範囲同士の見比べに手間がかかることを意識しておく必要があります。
4. 数は出るのに件数が合わないケース（原因診断）
エラーは出ないのに、手計算や別の集計方法と数字がズレる場合は、次の順番で確認していくと原因を特定しやすくなります。冒頭の私の失敗談も、このうち「4-1」が原因でした。
| 症状 | 疑うべき原因 | 確認方法 |
|---|---|---|
| 件数が実際より少ない／多い | 条件範囲同士の行数がズレている | 各条件範囲の最終行番号を見比べる |
| 条件を増やしたら急に0件になった | AND条件のつもりがOR条件を求めていた | 求めたい集計が「かつ」か「または」かを紙に書き出す |
| 特定の行だけ数えられない | 文字列に全角・半角や余分なスペースの表記ゆれがある | 条件を1つずつ外して数え直し、原因の条件を切り分ける |
| 期間指定で件数が合わない | 日付として入力されたセルと文字列のセルが混在している | 対象セルが右揃え（日付・数値）か左揃え（文字列）か確認する |
| 後からデータを増やしたら数字が変わった | 条件範囲を広く取りすぎて余計な行を拾っている | データ行と作業用メモを別シートに分ける |
4-1. 条件範囲同士の行数がズレている
冒頭でも触れた通り、条件範囲1をA2:A300にしたのに、条件範囲2をB2:B299のように短く指定してしまうケースです。エラーにならず、ただ集計結果だけがズレることもあるため、むしろ気づきにくい部類のミスです。
4-2. AND条件のつもりがOR条件を求めていた
COUNTIFS関数の条件はすべてAND（かつ）で判定されます。「AまたはB」を数えたいのに条件をそのまま並べてしまうと、「AかつB」の件数しか返ってきません。OR条件で数えたい場合は、先ほどの型4のように配列を使うか、COUNTIFSを複数回使って合算する必要があります。
4-3. 条件の文字列に表記ゆれがある
全角・半角の違いや、見た目では分からない余分なスペースが条件の一致を妨げます。条件が複数になる分、どの条件範囲に表記ゆれがあるのかを特定するのに時間がかかりやすいため、条件を1つずつ外しながら数え直す手順を知っておくと役立ちます。
4-4. 日付の条件で文字列と日付データが混在している
期間で絞り込む条件の中に、日付として入力されているセルと、文字列として入力されているセルが混在していると、条件が正しく機能しません。セルが右揃えか左揃えかを見るだけで、多くの場合は原因に気づけます。
4-5. セル結合された見出し行を条件範囲に含めてしまう
表の見出し部分が結合セルになっていると、条件範囲の開始位置を間違えて、見出し行そのものを範囲に含めてしまうことがあります。結合セルは値が左上のセルにしか入っていないため、想定と違う挙動になりやすい原因の一つです。
4-6. 条件範囲を広く取りすぎている
「後からデータが増えても大丈夫なように」と範囲を広めに取ると、表の下に置いていた集計用の数式やメモまで条件範囲に含まれてしまうことがあります。データ行と、それ以外の作業用のセルは、別のシートに分けておくと安全です。
5. COUNTIFとの使い分け
条件が1つだけで済む場合は、COUNTIFS関数ではなくシンプルにCOUNTIF関数を使う方が、数式が読みやすくなります。条件が1つなのに癖でCOUNTIFSを使ってしまうと、範囲と条件のペアが1組で終わる不自然な数式になり、後から見た人に「もう1つ条件があるのでは」と誤解させてしまうこともあります。COUNTIF関数の基本的な書き方や、単一条件でのよくある間違いについては、COUNTIF関数の使い方をわかりやすく解説｜数えたはずの件数が合わないときの原因で詳しく取り上げています。
また、件数ではなく金額や数量そのものを複数条件で合計したい場合は、COUNTIFSではなくSUMIFS関数を使います。書き方の違いや、合計がズレるときによくある原因はSUMIFS関数の使い方をわかりやすく解説｜複数条件の合計がズレるときに疑うべきポイントにまとめていますので、あわせて確認してみてください。
よくある質問
Q. COUNTIFSは何個まで条件を指定できますか？
仕様上は127個の条件範囲・条件のペアまで指定できますが、実務でそこまで条件を増やすことはほとんどありません。条件が5個、6個と増えて数式が読みづらくなってきたら、表の設計自体（列を分ける、ピボットテーブルを使うなど）を見直すタイミングかもしれません。
Q. COUNTIFSとピボットテーブルは、どちらを使うべきですか？
定型のレポートに毎回同じ形式で数値を差し込みたい場合はCOUNTIFS、集計の軸そのものを都度変えながら分析したい場合はピボットテーブル、という使い分けが基本です。既存の表のレイアウトを崩さずに集計欄だけを追加したい場合も、COUNTIFS関数の方が扱いやすいことが多くあります。
Q. 条件範囲に空白セルが混ざっていても大丈夫ですか？
大丈夫です。COUNTIFS関数は条件に一致しない行（空白セルを含む）を単純に数えないだけなので、空白セルがあることでエラーになったり、集計がずれたりすることはありません。
Q. 条件範囲を後から追加・変更するとき、注意することはありますか？
条件範囲を追加するときは、既存の条件範囲とペアになっている検索条件を、正しい順番のまま維持することが大切です。数式バーの中で範囲と条件のペアがずれてしまうと意図した集計にならないため、追加や変更をした後は、必ず数式バーを開いて範囲と条件の対応関係を目で確認してください。
もう一歩踏み込んで、複数の条件や複数の関数が絡み合う複合パターンまで整理して確認したい方向けに、note会員限定でより実務寄りの内容をまとめています。気になる方はもっと実務寄りの話を知りたい方へ｜note会員限定コンテンツのご案内からご覧ください。
まとめ
COUNTIFS関数は、複数条件で件数を数えたいときの定番関数です。COUNTIF関数の考え方がそのまま応用できる一方で、条件範囲が増える分、範囲のズレやAND・ORの取り違えといった、複数条件ならではのミスも起きやすくなります。数が合わないと感じたときは、次の順番で確認してみてください。
- 条件範囲同士の行数が完全に一致しているか確認する
- 求めたい集計が「AND（かつ）」か「OR（または）」かを先に整理する
- 条件の文字列・日付の表記ゆれを疑う
- 条件範囲を広く取りすぎていないか、作業用のセルを巻き込んでいないか確認する
- 条件が増えすぎたら、表の設計やピボットテーブルの活用も検討する
コメント