COUNTIFS関数は、複数の条件をすべて満たすセルの個数を数える関数です。条件範囲と条件をペアで並べるのが書き方の骨格。つまずくのは「範囲の形をそろえること」と「空白の扱い」の2つに集中しています。
この記事でわかること
- COUNTIFS関数のペア構造と、条件を増やす書き方
- 日付で期間を区切る方法と、月ごとのクロス集計の組み方
- 空白・空白以外の数え方と、件数の合計が合わなくなる理由
#VALUE!や0件になるときの原因と、切り分けの順番- OR条件・重複を除いた種類数など、COUNTIFSでは書けないことの代わりの手
COUNTIFS関数の構文と引数
構文
=COUNTIFS(条件範囲1, 条件1, [条件範囲2, 条件2], ...)| 引数 | 指定するもの | 注意点 |
|---|---|---|
| 条件範囲1 | 1つ目の条件を判定するセル範囲 | すべての条件範囲を同じ行数・同じ形にそろえる |
| 条件1 | 1つ目の条件 | 比較演算子やワイルドカードは " で囲む |
| 条件範囲2・条件2 | 2つ目以降 | 2つで1組。組を増やすほど条件が足される |
SUMIFS関数と違い、先頭に「数える範囲」を置きません。数える対象は条件範囲そのもので、すべての条件を満たした行が1件としてカウントされます。
条件どうしは常に「かつ」で結ばれます。「または」は直接書けないため、後半で別の組み立てを使います。
COUNTIFS関数はExcel 2016以降のどのバージョンでも使えます。
基本の使い方
A列に日付、B列に都道府県、C列にステータス、D列に金額が並ぶ一覧表を例にします。東京で10万円以上の件数を数える式です。
=COUNTIFS($B$2:$B$100, "東京", $D$2:$D$100, ">=100000")条件を直接書かず、集計表のセルを参照させると使い回せます。F列に都道府県、1行目にステータスを並べたクロス集計表なら、次の形です。
=COUNTIFS($B$2:$B$100, $F2, $C$2:$C$100, G$1)$F2 は列だけ固定、G$1 は行だけ固定。1本の式を右にも下にもコピーするだけで表全体が埋まります。範囲側の $B$2:$B$100 は両方固定です。
条件の書き方は4通り
| やりたいこと | 書き方 | 例 |
|---|---|---|
| 値と一致 | そのまま書く | "東京" ／ 100 |
| 大小で絞る | 演算子ごと " で囲む | ">=100000" |
| セルの値と演算子を組む | "演算子"&セル | ">="&$G$1 |
| 〜を含む | * で挟む | "*支店*" |
引っかかりやすいのは3番目です。">=G1" と書くと「G1という文字列以上」という意味になり、1件も数えられません。演算子だけを引用符に入れ、& でセル参照をつなぎます。
範囲の形は必ずそろえる
条件範囲の行数がずれていると #VALUE! になります。どの範囲も同じ行番号で始め、同じ行番号で終えるのが鉄則です。$B$2:$B$100 と $D$2:$D$50 の組み合わせは動きません。
行が増える表なら、範囲を $B$2:$B$1000 のように余裕を持たせるか、テーブル（Ctrl＋T）にして構造化参照で書くと保守が楽になります。
日付で期間を区切る
同じ列を2回指定して、開始日と終了日ではさみます。
=COUNTIFS($A$2:$A$100, ">="&DATE(2026,4,1), $A$2:$A$100, "<"&DATE(2026,5,1))日付を ">=2026/4/1" と文字列で直接書くと、環境の日付設定によって解釈が変わることがあります。DATE関数か、日付を入れたセルの参照で指定するのが確実です。
終了日は「翌日より前」で切る
Excelの日付は内部で数値として持たれ、時刻はその小数部分に入ります。4月30日 14:20 は「4月30日」より大きい値なので、"<="&DATE(2026,4,30) ではその日の時刻つきデータがまるごと漏れます。
上の式のように <= を < に変え、終了日を翌日にすれば、時刻の有無にかかわらず結果が変わりません。システムから書き出したログや受注データを数えるときは、この形を既定にしておくと安全です。
月ごとのクロス集計にする
F列に月初日を並べ、1行目にステータスを置いた表なら、次の1本で埋まります。EDATE関数は指定の月数後の同じ日を返します。
=COUNTIFS($A$2:$A$1000, ">="&$F2, $A$2:$A$1000, "<"&EDATE($F2,1), $C$2:$C$1000, G$1)条件が3組になっても構造は同じです。日付の2組で月を挟み、3組目で軸を切る。F列を下へ伸ばすだけで月次推移表になります。
空白の扱いと、件数が合わないとき
空白を条件にする書き方
| 条件 | 数えるもの |
|---|---|
"<>" | 空白以外のセル |
"=" | 空白のセル |
"*" | 文字列が入っているセル（数値は数えない） |
「担当者が未入力の案件だけを数える」なら "="、「入力済みだけを数える」なら "<>" を1組足します。
⚠️ 条件に "" を書く方法は避けてください。空欄と「空文字列を返す数式」の扱いが紛らわしく、結果が読み手の想定とずれる原因になります。空白の判定はCOUNTBLANK関数に任せるほうが確実です。
見た目が空でも「空白でない」ことがある
=IF(A2="", "", B2) のような数式が入っているセルは、画面上は空に見えても中身は数式です。この種のセルはCOUNTA関数と同じく「データあり」の側に数えられます。
そのため、次のような食い違いが起きます。
- 目視では30件が空欄に見える
"<>"で数えると、そのうち何件かが「入力済み」に入る- 集計表の合計が、実際の行数と合わなくなる
件数の検算を1本置く
クロス集計を作ったら、各セルの合計が全体件数と一致するかを必ず確かめてください。
=SUM(集計表の範囲) = COUNTA($B$2:$B$100)FALSE が返ったら、次の3つを疑います。
- 表記ゆれ — 「東京」と「東京都」、全角と半角が混ざっている
- 空白 — 軸の列に未入力の行があり、どの区分にも入っていない
""を返す数式 — 空に見える行が「入力済み」側で数えられている
集計表に「（未入力）」の行を1本足し、"=" で数えておくと、合計が必ず一致するようになります。検算が通る集計表は、後から見た人も安心して使えます。
うまくいかないときの原因と直し方
| 症状 | 主な原因 | 直し方 |
|---|---|---|
#VALUE! | 条件範囲の行数がそろっていない | すべての範囲を同じ行番号でそろえる |
#VALUE! | 参照先の別ブックが閉じている | ブックを開く。またはSUMPRODUCTで書き換える |
| 0件になる | 演算子とセル参照を & でつないでいない | ">="&$G$1 の形に直す |
| 0件になる | 前後の空白・全角半角の混在 | TRIM・ASCで整える |
| 0件になる | 複数の値を1つの条件欄に並べた | ORは配列定数か足し算で書く |
| 数が合わない | 数値が文字列として入っている | 左寄せなら文字列。「区切り位置」で変換する |
| 最終日の分が数えられない | 日付に時刻が入っている | "<="&終了日 を "<"&翌日 に変える |
| 合計が全体件数と合わない | 空白／"" を返す数式 | 「（未入力）」の行を足して検算する |
| 非表示の行まで数えられる | COUNTIFSは表示状態を見ない | SUBTOTAL関数かAGGREGATE関数を使う |
| 再計算が重い | 範囲に列全体を指定している | $B$2:$B$5000 のように行数を区切る |
0件のときは条件を1組ずつ外す
式全体をにらむより、条件を減らして切り分けるほうが速く済みます。条件を1組だけ残して数え、それでも0件ならその条件が原因。あとは =COUNTIF($B$2:$B$100, $F2) で単独チェックすれば、「セル参照と演算子のつなぎ方」か「表記ゆれ」のどちらかに行き着きます。
COUNTIFSでは書けない3つと、その代わり
① OR条件（どちらか一方）
条件を波かっこで並べ、結果を合計します。
=SUM(COUNTIFS($B$2:$B$100, {"東京","大阪","名古屋"}))⚠️ 違う列どうしでORを取るときは二重計上に注意してください。「東京の件数」と「10万円以上の件数」を単純に足すと、両方に当てはまる行が2回数えられます。この場合は重なりを引きます。
=COUNTIFS($B$2:$B$100,"東京") + COUNTIFS($D$2:$D$100,">=100000") - COUNTIFS($B$2:$B$100,"東京",$D$2:$D$100,">=100000")② 重複を除いた「種類の数」
COUNTIFSが返すのは件数であって、種類の数ではありません。Excel 2021以降ならUNIQUE関数が最短です。
=COUNTA(UNIQUE($B$2:$B$100))2019以前の環境では、次の形が使えます。ただし範囲に空白セルがあると #DIV/0! になるため、範囲は実データの行数ぴったりに指定します。
=SUMPRODUCT(1/COUNTIF($B$2:$B$100, $B$2:$B$100))③ フィルタで絞った行だけを数える
COUNTIFSは行の表示・非表示を見ません。画面に出ている行だけを数えたいなら、SUBTOTAL関数（集計方法 3）かAGGREGATE関数を使います。
似た関数との使い分け
| 関数・機能 | 対応バージョン | 選ぶ場面 |
|---|---|---|
| COUNTIFS | 2016〜 | 条件が2つ以上。条件1つでも書ける |
| COUNTIF | 2016〜 | 条件が1つだけの短い式にしたい |
| COUNTA | 2016〜 | 条件なしで、入力済みのセル数を数える |
| SUMPRODUCT | 2016〜 | 複雑な条件を組みたい。閉じた別ブックも参照できる |
| SUBTOTAL | 2016〜 | フィルタで絞った表示中の行だけを数える |
| FILTER＋ROWS | 365／2021〜 | 数えるだけでなく、該当行そのものも見たい |
| ピボットテーブル | 2016〜 | 集計軸を何度も入れ替えて確かめたい |
SUMPRODUCTへの書き換え
同じ集計は、条件式を掛け算でつないだSUMPRODUCTでも書けます。
=SUMPRODUCT(($B$2:$B$100="東京")*($D$2:$D$100>=100000))閉じた別ブックを参照できるのがCOUNTIFSにない利点です。一方で処理は重く、範囲を広く取るほど再計算が遅くなります。通常はCOUNTIFS、別ブック参照や複雑な条件のときだけSUMPRODUCT、という分け方が扱いやすくなります。
集計軸が固まっていないうちはピボットテーブルのほうが速く、式の保守も要りません。件数を決まった位置のセルに出したい場合だけCOUNTIFSを選ぶ、という順序で考えると迷いません。
よくある質問
Q1：条件はいくつまで指定できますか？
上限は127組とされていますが、実務でこの数に当たることはまずありません。条件が5つを超えたあたりから式が読めなくなるので、作業列で条件をまとめるか、ピボットテーブルへ切り替えるほうが保守しやすくなります。
Q2：条件範囲の大きさが違うとどうなりますか？
#VALUE! になります。COUNTIFSは各範囲の同じ位置どうしを突き合わせて判定するため、形がそろっていないと成立しません。行数だけでなく、列方向の範囲と行方向の範囲を混ぜた場合も同様です。
Q3：「東京以外」を数えると空欄も入りますか？
"<>東京" の扱いは紛らわしいので、空白以外の条件を1組足すのが確実です。=COUNTIFS($B$2:$B$100, "<>東京", $B$2:$B$100, "<>") と書けば、未入力の行を確実に除いて数えられます。
Q4：別のシートやブックの範囲を数えられますか？
別シートは =COUNTIFS(データ!$B$2:$B$100, "東京") の形で問題なく数えられます。別ブックも指定はできますが、参照先のブックを閉じると #VALUE! になります。継続して使う集計表なら、同じブック内に取り込んでおくのが確実です。
Q5：数値と文字列が混ざった列でも正しく数えられますか？
条件の型と一致するものだけが数えられます。"100" のように文字列として入力された数値は、>=100 の条件では拾われません。列を選んで「区切り位置」を実行すると、数値へまとめて変換できます。左寄せか右寄せかで見分けられます。
Q6：セルの色で数えられますか？
数えられません。COUNTIFSが見ているのはセルの値だけで、書式は判定できないためです。色分けの基準になっている値（ステータス列など）を条件にして数えてください。
まとめ
- 条件範囲と条件は必ず2つで1組。先頭に数える範囲は置かない
- すべての範囲を同じ行数・同じ形にそろえる。ずれると
#VALUE! - 期間は同じ列を2回。終了日は「翌日より前」で切ると時刻の漏れが消える
- 空白は
"="、空白以外は"<>"。""は使わない - 集計表は合計と全体件数の検算を1本置く。合わなければ表記ゆれか空白
※関数の対応バージョンはExcelの一般提供時点の情報にもとづいています。お使いの環境や更新プログラムの適用状況により、利用できる関数や表示が異なる場合があります。
