ExcelのCOUNTIFSで条件を増やした途端に結果が0になる――特に「日付の範囲」を追加したときは、数式の書き方よりも“データ側”の落とし穴が原因であることがほとんどです。この記事では、COUNTIFSで日付範囲を指定すると0になる理由を分解し、文字列日付の変換・ロケール差の回避・時刻混在の対策まで、実務で再発させない手順として整理します。
COUNTIFSで日付範囲を追加すると0になる症状を整理
まず前提として、COUNTIFSは「指定した範囲の値」と「条件」を突き合わせて件数を数える関数です。商品名やフレーバーなど“文字列条件”だけなら正しく数えられるのに、日付条件を足した瞬間に0になる場合、数式のミスよりも次のどちらかが典型です。
| よくある状況 | 見えている現象 | 可能性が高い原因 |
|---|---|---|
| 3条件までは合うが、日付の範囲条件を足すと0 | 該当があるはずなのに全件不一致になる | 日付列が「文字列」扱い／日付の解釈がロケールとズレている |
| 同じ日付を見ているのに、並べ替え順が変 | 2025/7/2の次に2025/10/1が来るなど | 文字列として並べ替えている（=日付ではない） |
| 範囲の上限・下限の書き方を変えても改善しない | 「>=」や「<=」を変えても0 | 比較の前提となるデータ型が違う（数値の日付 vs 文字列） |
| 条件に「< 8/11/2025」を入れたら急に0 | 上限日で全滅する | 日付に時刻が混ざっている／上限日の境界設計が不適切 |
結論から言うと、COUNTIFSの日付範囲が0になる最大原因は「日付列が本当は日付ではない」ことです。表示が“それっぽい”だけで、Excel内部では文字列になっているケースが多発します。
COUNTIFSの日付条件が一致する仕組み（ここを押さえると早い）
Excelの日付は、内部的には連続した数値（シリアル値）として保存されています。たとえばWindowsの一般的な設定（1900日付システム）では、2025/07/11は「45849」のような数値です。セルの表示形式が「日付」になっていると、見た目は2025/07/11のように表示されますが、実体は数値です。
COUNTIFSで日付を比較する条件（例：">=2025/07/11"や"<2025/08/11"）は、結局のところ「対象範囲の値（数値）」と「条件で指定した日付（数値）」を比較できる状態でないと一致しません。つまり、日付列が文字列のままだと、比較が成立せず全件不一致になりやすいのです。
| 見た目 | セルの実体（例） | Excelでの扱い | COUNTIFSの比較 |
|---|---|---|---|
| 2025/07/11 | 45849 | 数値（=日付） | OK（>=や<で範囲判定できる） |
| 2025/07/11 | “2025/07/11” | 文字列 | NGになりやすい（数値比較できない／解釈がズレる） |
| 2025/07/11 13:05 | 45849.545… | 数値（=日付+時刻） | 境界次第で漏れる（上限の<指定が特に影響） |
この「見た目は日付だが、実体が文字列」という状態こそが、COUNTIFSの“日付範囲を追加すると0”問題の本丸です。
最初にやるべき確認：日付が「文字列」か「日付（数値）」か
見た目（表示形式）だけで判断しない
よくある誤解は「セルの表示形式を“日付”に変えたから大丈夫」というものです。表示形式は見た目を変えるだけで、文字列が自動で日付（数値）に変換されるとは限りません。CSV/TSVの取り込み、外部システムからの貼り付け、Webからのコピーなどでは、日付が文字列のまま入ってくることが多いです。
目安として、次のような症状があれば文字列日付を疑ってください（ただし環境や設定によって例外もあります）。
- 日付が左寄せになっている（数値は通常右寄せになりがち）
- 並べ替えが時系列にならない
- フィルターで「日付フィルター」が出ず「テキストフィルター」になる
ISNUMBER / ISTEXT で“確定診断”する
最も確実なのは、検査用の列を1つ作って、日付セルが数値かどうかを判定することです。例として、日付がB列（B2から下）にある想定で書きます。
- 日付が数値か確認：
=ISNUMBER(B2) - 日付が文字列か確認：
=ISTEXT(B2)
結果がTRUEなら、その性質が当たっています。COUNTIFSで日付範囲を扱うなら、基本的に日付列はISNUMBERがTRUEになる状態が望ましいです。
| チェック式 | TRUEになるとき | 次のアクション |
|---|---|---|
=ISNUMBER(B2) | 日付が数値（=本物の日付） | ロケール差・時刻混在・境界条件を疑う |
=ISTEXT(B2) | 日付が文字列 | まず日付へ変換（これが最優先） |
=ISBLANK(B2) | 空白 | 空白行の扱い（除外/含む）を決める |
=ISERROR(B2) | #VALUE!等のエラー | エラー行を除外するかデータを修正 |
日付が文字列だった場合の変換方法（おすすめ順）
ISNUMBERがFALSEで、ISTEXTがTRUEなら、COUNTIFSの前に日付を「本物の日付（数値）」に直す必要があります。ここでは実務で使いやすい順に紹介します。
区切り位置（Text to Columns）で一括変換する
最短で直ることが多い王道が、Excelの「区切り位置」です。手順は次の通りです。
- 日付列を選択する
- ［データ］タブ → ［区切り位置］
- 区切り文字は不要なのでそのまま進む
- 列のデータ形式で「日付」を選び、YMD / MDY / DMY を正しく指定して完了
ポイントは最後の「日付の順序」です。たとえば 7/11/2025 は、環境によって2025年7月11日（MDY）にも2025年11月7日（DMY）にも解釈できてしまいます。ここがズレると、見た目は日付でも中身が想定日と違い、COUNTIFSが全滅します。
関数で日付に変換して置き換える（作業列方式）
変換がうまくいかない場合や、元データを壊したくない場合は、隣の列に変換式を入れて“正しい日付列”を作る方法が堅実です。
代表的なパターンは次の通りです。
- 文字列がExcelに解釈できる形なら：
=DATEVALUE(B2) - 数値っぽい文字列なら：
=VALUE(B2) - 「見た目が日付で中身が文字列」なら：
=--B2（二重マイナスで数値化）
うまく変換できたら、その列をコピー → 値貼り付けして、元の日付列と差し替えます。変換後にISNUMBERがTRUEになることを必ず確認してください。
Power Queryで型を「日付」に変換する（取り込みが原因なら最強）
データの出どころがCSVやシステム出力で、毎回同じ問題が起きるなら、Power Queryで「列のデータ型」を日付に固定するのが再発防止に効きます。取り込み手順の中で日付列を選び、データ型を「日付」または「日付/時刻」に変更してから読み込むだけで、COUNTIFS側のトラブルが激減します。
| 方法 | 向いているケース | メリット | 注意点 |
|---|---|---|---|
| 区切り位置 | 一度きりの修正／手早く直したい | 速い・一括で効く | YMD/MDY/DMYの選択ミスに注意 |
| 関数で変換 | 元データを保護したい／検証しながら直したい | 差分確認がしやすい | 文字列の形式がバラバラだと追加処理が必要 |
| Power Query | 定期的に取り込む／同じ失敗を繰り返す | 再現性・自動化に強い | クエリ更新の運用が必要 |
ロケール差（7/11/2025問題）を確実に回避するCOUNTIFSの書き方
日付が“本物の日付”になっていても、条件側の書き方がロケール依存だと、環境によって期待どおりに解釈されないことがあります。とくに 7/11/2025 のような表記は、米国形式（MDY）か日/月/年（DMY）かで意味が変わります。
この問題を避ける最も安全な方法は、条件の中にDATE関数を使って日付を数値で渡すことです。COUNTIFSの条件は文字列で指定しますが、比較する日付部分はDATE(年,月,日)で作り、&でつなぎます。
質問のケース（商品=Chewing Gum、フレーバー=Cinnamon、日付が2025/07/11以上かつ2025/08/11未満）なら、次のように書くのが定番です。
=COUNTIFS(
DETAILS!$M$2:$M$5000,"Chewing Gum",
DETAILS!$L$2:$L$5000,"Cinnamon",
DETAILS!$B$2:$B$5000,">="&DATE(2025,7,11),
DETAILS!$B$2:$B$5000,"<"&DATE(2025,8,11)
)この形なら、PCの地域設定や表示形式に左右されにくく、COUNTIFSの日付範囲が0になるリスクを大幅に減らせます。
「< 2025/08/11」なのか「<= 2025/08/11」なのかを整理する
日付範囲条件は、どこまでを含めるか（境界）を先に決めるのがコツです。実務では次の2パターンがよく使われます。
| 目的 | 推奨する条件 | 理由 |
|---|---|---|
| 開始日以上〜終了日未満（半開区間） | ">="&開始日 と "<"&終了日 | 時刻が混ざっていても漏れにくい（終了日は“次の境界”として扱える） |
| 開始日以上〜終了日当日まで含む | ">="&開始日 と "<"&(終了日+1) | 終了日が「当日23:59:59」までを自然に含められる |
たとえば「2025/08/11のデータも含めたい」なら、終了条件を "<"&DATE(2025,8,12) にするのが安全です（終了日+1を“未満”で切る）。一方で「8/11を含めない」なら、質問のように "<"&DATE(2025,8,11) で問題ありません。
それでも0になるときに見るべき追加ポイント
日付列を数値に変換し、DATE関数で条件を組んでも0のままなら、次の“実務あるある”を疑います。ここは一つずつ潰すと確実です。
日付に時刻が含まれていて、上限条件で漏れている
システム出力の日時（例：2025/08/11 13:00）を日付列として扱っている場合、セルの実体は 45880.5416… のように小数を含む数値になります。このとき、条件が "<"&DATE(2025,8,11) だと、2025/08/11当日のデータはすべて除外されます（8/11の0:00未満しか入らないため）。
対策は次のどちらかです。
- “日付だけ”で比較したい：別列に
=INT(B2)を作って日付だけに丸め、その列でCOUNTIFSする - “終了日当日まで含めたい”：終了条件を
"<"&(終了日+1)にする
空白・全角スペース・ノンブレークスペースが混ざっている
日付とは別条件（商品名やフレーバー）が、見た目は一致しているのに一致しない場合、余計な空白や不可視文字が混ざっていることがあります。Webページからコピーしたデータは、半角スペースではなくノンブレークスペースが入っていることもあります。
対策としては、集計対象列に対して次のような“正規化”を行うのが有効です。
- 前後の余計な空白を除去：
=TRIM(A2) - 印字できない文字を除去：
=CLEAN(A2) - ノンブレークスペース置換：
=SUBSTITUTE(A2,CHAR(160)," ")
「日付っぽい文字列」が混在している（ハイフンやドット、月名など）
同じ列に 2025-07-11、2025/07/11、11-Jul-2025 のような表記揺れが混ざると、DATEVALUEで変換できる行とできない行が出ます。変換に失敗した行はエラーになり、COUNTIFSの対象として扱えない（または意図せず除外される）ことがあります。
この場合は、Power Queryで取り込み時に型変換・ロケール指定を行う、または文字列を分割して DATE(年,月,日) を組み立てるなど、データの正規化を先にやるのが近道です。
範囲の行数がズレていて、参照が想定と違う
COUNTIFSは、条件範囲のサイズ（行数・列数）が揃っていないとエラーになりますが、似た構造のシートを複製して一部の範囲だけ変更した場合など、参照先がズレて“そもそも該当行を見ていない”ということがあります。特に、別シート参照（例：DETAILS!$B$2:$B$5000）を複数使うときは、すべての範囲が同じ行幅になっているか、念のため確認してください。
| チェック項目 | 確認方法 | 直し方の例 |
|---|---|---|
| 日付列は数値か | =ISNUMBER(対象セル) | 区切り位置／DATEVALUE／Power Queryで変換 |
| 日付の解釈がロケール通りか | 7/11/2025が7月11日か11月7日かを検算 | 条件はDATE関数に統一、取り込み時のロケールを固定 |
| 時刻が混ざっていないか | =MOD(対象セル,1) が0以外なら時刻あり | INTで日付だけにする／終了条件を「<終了日+1」へ |
| 文字列条件に不可視文字がないか | LENで長さ比較、TRIM後の比較 | TRIM/CLEAN/SUBSTITUTEで正規化列を作る |
| 条件範囲が同じサイズか | 数式の範囲を見比べる | 参照範囲を統一、テーブル化して構造化参照にする |
実務で再発させない：安全な集計テンプレート
原因を潰しても、運用でまた“文字列日付”が混ざると同じ事故が起きます。再発を防ぐために、COUNTIFS側だけでなくデータ設計も少しだけ整えるのがおすすめです。
開始日・終了日をセルに置いて、DATE関数で管理する
数式に直書きすると更新時にミスが起きやすいので、開始日と終了日をセルに置き、そこを参照する形にすると安全です。たとえば、集計シートのF2に開始日、G2に終了日（“含めたい最終日”）を入れる運用にします。
- F2：2025/07/11（本物の日付）
- G2：2025/08/11（本物の日付）
このとき、終了日まで含めたいなら「< 終了日+1」が便利です。
=COUNTIFS(
DETAILS!$M$2:$M$5000,$A$2,
DETAILS!$L$2:$L$5000,$B$2,
DETAILS!$B$2:$B$5000,">="&$F$2,
DETAILS!$B$2:$B$5000,"<"&($G$2+1)
)商品名やフレーバーもセル参照にしておくと、集計条件を変えても式を触らずに済みます。運用ミスが減るので、SEO的に検索されやすい“COUNTIFS 複数条件 集計”の実務にもそのまま流用できます。
テーブル（ListObject）化して構造化参照にする
範囲参照（$B$2:$B$5000）のままだと、データが増えたときに範囲不足で数え漏れます。集計対象をテーブル化し、列名で参照するとメンテナンスが楽になります。
=COUNTIFS(
DETAILS[商品],"Chewing Gum",
DETAILS[フレーバー],"Cinnamon",
DETAILS[日付],">="&DATE(2025,7,11),
DETAILS[日付],"<"&DATE(2025,8,11)
)テーブルにしておけば、データ追加で範囲が自動拡張され、COUNTIFSの条件範囲ズレも起きにくくなります。
よくある質問（COUNTIFSの日付範囲が0になる周辺）
セルの表示形式を「日付」にしたのに直りません
表示形式の変更は“見た目”だけが変わることがあります。ISNUMBERでFALSEのままなら、文字列のままです。区切り位置やDATEVALUEなどで実体を日付（数値）に変換してください。
日付条件を “>=7/11/2025” と書いたら環境で結果が変わりました
月/日/年（MDY）と日/月/年（DMY）の解釈違いが原因です。COUNTIFSでは ">="&DATE(2025,7,11) のようにDATE関数を使うと、ロケール差を避けられます。
上限を「<=」にしたら直ることもありますか？
データに時刻が含まれる場合は、むしろ「<=終了日」だと想定外の漏れや含みが起きやすいです。おすすめは「<終了日+1（未満）」です。日付だけで管理できているなら「<=終了日」でも動きますが、混在しがちな実務では前者が安全です。
まとめ：COUNTIFSで日付範囲を追加すると0になるときの最短ルート
COUNTIFSで日付の範囲条件を加えた瞬間に0になる場合、まず疑うべきは日付列が文字列になっていること、次に疑うべきは7/11/2025のようなロケール依存表記です。ISNUMBER/ISTEXTでデータ型を確定診断し、文字列なら日付（数値）に変換したうえで、条件はDATE関数で組み立てる――この順で対応すれば、ほとんどのケースは解決できます。
最後に、迷ったら次の形を基本形として使ってください。
- 開始日以上：
">="&DATE(年,月,日) - 終了日当日まで含める：
"<"&(DATE(年,月,日)+1)
これで「COUNTIFS 日付 範囲 0になる」問題の再発を、かなりの確率で防げます。
コメント