SUMIFSやCOUNTIFSで集計したのに、こんな結果が出て手が止まったことはありませんか。
- 日付で期間を絞った途端、合計が0になる
- 月末の分だけ、なぜか集計から漏れている
- 「空欄の行」や「0以外」を数えたら件数が合わない
- #VALUE! が出て、どこが悪いのか見当がつかない
関数の形は使い方の記事どおりなのに、条件を書き足したところで結果がおかしくなる。締め日の前にこれが起きると焦りますよね。
原因の多くは、条件（検索条件）の書き方にあります。Excelの条件式には、日付の渡し方や空欄の表し方など、見た目と中身がずれやすい箇所がいくつかあるんです。
この記事では、ExcelのSUMIFS・COUNTIFSで条件が効かない原因を7つに整理しました。原因ごとに「誤式→正式→結果の違い」の表を置き、10行のサンプルデータで戻り値を並べています。
なお、関連する記事との使い分けは次のとおり。
- データ側の原因は、Excel集計・検索が合わない原因5つと直し方で扱っています。文字列の数値・見えない空白・全角半角の混在などです
- 関数の基本の書き方は、SUMIFS関数の使い方とCOUNTIFS関数の使い方を見てください
- この記事は「条件式の書き方」に絞って解説します
SUMIFS・COUNTIFSは Excel 2007 以降で使える関数で、ここで扱う原因はバージョンによらず共通です。
ExcelのSUMIFS・COUNTIFSの条件が効かない症状の早見表と切り分けの順番
まずは、いま起きている症状から原因の番号を絞り込みましょう。番号は、このあと解説する原因①〜⑦に対応しています。
症状→原因番号の早見表
| いま起きている症状 | 疑う原因 |
|---|---|
| 日付で絞ると合計・件数が0になる | ①・② |
| 月末（最終日）の分だけ集計から漏れる | ① |
| 数式を確定できない／演算子を付けたら0になった | ② |
| 空欄の行の件数が思っていたのと違う | ③ |
| 「0以外」を数えたら空欄の行まで入ってくる | ④ |
| #VALUE! が出る | ⑤・⑥ |
| エラーは出ないのに合計が1行ずれている | ⑤ |
| 長い文章を条件にすると結果がおかしい | ⑥ |
| 違う番号なのに一致扱いで数えられる | ⑦ |
0になる症状は原因①②、件数がずれる症状は原因③④⑦に集中しています。エラーが出るなら、まず原因⑤⑥を疑ってみてください。
切り分けチェック5項目
早見表で絞れないときは、次の順に確認すると遠回りせずに済みます。
- 範囲のサイズはそろっているか：合計範囲と条件範囲の行数が同じか（原因⑤）
- 引用符と&の位置は正しいか：演算子は
"の内側、セル番地は外側に置いて&でつなぐ（原因①②） - 日付は数値として入っているか：
=ISNUMBER(A2)が TRUE になるか - 空欄の種類はどれか：本当に何もないセルか、数式の
=""か（原因③④） - 文字数・桁数が長すぎないか：255文字を超える文章、16桁以上の番号（原因⑥⑦）
1と2は、式を眺めるだけで判定できます。3〜5はセルの中身を調べる作業になるので、前の2つで見つからなければ進みましょう。
この記事で使うサンプルデータ
以下の表を A1:F11 に入力すると、記事中の式をそのまま試せます。1行目は見出しで、データは2〜11行目の10行です。
| 行 | A：日付 | B：担当 | C：備考 | D：値引き | E：売上 | F：会員番号 |
|---|---|---|---|---|---|---|
| 2 | 2026/9/30 | 佐藤 | 至急 | 0 | 12,000 | 1234567890123456 |
| 3 | 2026/10/1 | 鈴木 | （空白） | 500 | 8,000 | 1234567890123457 |
| 4 | 2026/10/5 | 佐藤 | ="" | （空白） | 15,000 | 9876543210987654 |
| 5 | 2026/10/12 | 高橋 | 返品 | 0 | 6,000 | 1111222233334444 |
| 6 | 2026/10/15 | 鈴木 | （空白） | 1,000 | 20,000 | 5555666677778888 |
| 7 | 2026/10/20 | 佐藤 | ="" | （空白） | 9,000 | 2222333344445555 |
| 8 | 2026/10/28 | 高橋 | 至急 | 300 | 11,000 | 3333444455556666 |
| 9 | 2026/10/31 10:00 | 佐藤 | （空白） | 0 | 7,000 | 4444555566667777 |
| 10 | 2026/10/31 17:30 | 鈴木 | 至急 | 200 | 13,000 | 6666777788889999 |
| 11 | 2026/11/1 | 高橋 | ="" | （空白） | 14,000 | 7777888899990000 |
入力するときのポイントは次の3つ。
- 「（空白）」は何も入力しないセル、
=""は数式で空の文字列を返すセルです - F列は、セルの書式を「文字列」にしてから入力します（数値のままだと16桁目が0に変わるため）
- 条件用に、H2 に開始日
2026/10/1、H3 に終了日2026/10/31を入れておきます
E列の売上は全10行で合計115,000円。このあとの戻り値は、すべてこの表から計算した値です。
型・空白・全角半角が原因ならデータ側を直す
チェックの3番で =ISNUMBER(A2) が FALSE になった場合、日付が文字列として入っています。この状態では、条件式をどう直しても日付として比べられません。
数字の前後の空白や全角半角の混在も、同じくデータ側の問題です。式を直す前に、Excel集計・検索が合わない原因5つと直し方でデータを整えてください。
日付・比較演算子の書き方でSUMIFSの条件が効かない原因
ここからは原因を1つずつ見ていきます。最初は、相談がいちばん多い日付と比較演算子の書き方です。
原因①：日付を “>=H2” と直書きしている（月末が漏れる問題も）
期間で絞るときに、セル番地まで引用符の内側に入れてしまうパターンです。">=H2" と書くと、Excelは「H2 というセルの値」ではなく「H2 という文字」と比べます。
A列の日付は数値なので、文字との比較には1件も当てはまりません。結果は0。正しくは、演算子だけを引用符で囲み、セル番地を & でつなぎます。
| 区分 | 式 | 戻り値 |
|---|---|---|
| 誤式（番地を直書き） | =SUMIFS(E2:E11,A2:A11,">=H2",A2:A11,"<=H3") | 0 |
| 正式だが月末が漏れる | =SUMIFS(E2:E11,A2:A11,">="&H2,A2:A11,"<="&H3) | 69,000 |
| 正式（翌日未満で指定） | =SUMIFS(E2:E11,A2:A11,">="&H2,A2:A11,"<"&H3+1) | 89,000 |
COUNTIFSで同じ条件を書くと、戻り値は順に 0件・6件・8件。
">="&H2 は、Excelの内部では ">=46296" という条件に組み立てられます。46296 は 2026/10/1 のシリアル値（日付を表す通し番号）です。
ここで見落としやすいのが、2行目の式で起きる月末の漏れ。9行目と10行目は、10/31 に時刻が付いたデータです。
シリアル値では、時刻は小数部で表されます。10/31 10:00 は 46326.41… となり、終了日 "<="&H3（46326以下）を超えてしまうんです。その結果、2行分の 20,000 円が集計から外れます。
終了日は「以下」ではなく、翌日の0時より前（翌日未満）で指定しましょう。時刻付きのデータがあってもなくても、同じ式で通用します。
=SUMIFS(E2:E11,A2:A11,">="&H2,A2:A11,"<"&H3+1)日付を式の中に直接書きたいときは、DATE関数で渡しましょう。
=SUMIFS(E2:E11,A2:A11,">="&DATE(2026,10,1),A2:A11,"<"&DATE(2026,11,1))この式も 89,000 を返します。">=2026/10/1" のように、日付を文字のまま書く方法もあります。ただ、解釈が環境に左右される可能性があるので、DATE関数かセル参照がおすすめ。
シリアル値の仕組みはExcelのシリアル値の解説で詳しく紹介しています。
原因②：比較演算子の引用符忘れ・&抜け・全角入力
条件式の基本ルールはシンプルです。演算子は引用符の内側、セル番地や関数は外側。この組み合わせが崩れると、式が通らないか、0が返ります。
| 区分 | 式 | 結果 |
|---|---|---|
| 誤式（引用符なし） | =COUNTIFS(A2:A11,>=H2) | 数式を確定できない |
| 誤式（&抜け） | =COUNTIFS(A2:A11,">="H2) | 数式を確定できない |
| 誤式（全角の演算子） | =COUNTIFS(A2:A11,"＞＝"&H2) | 0 |
| 正式 | =COUNTIFS(A2:A11,">="&H2) | 9 |
引用符忘れと&抜けは、数式の文法として成り立ちません。Enterで確定しようとすると、数式に問題があるという趣旨のメッセージが出て止まるはず。メッセージの文言はバージョンによって異なります。
やっかいなのは、日本語入力がオンのまま ＞＝ と打ってしまうケース。全角の記号は演算子として扱われず、「＞＝46296」という文字と一致するセルを探す形になると考えられます。エラーが出ないぶん、0の原因に気づきにくいんですよね。
数値を直接書く場合は、演算子と数値をまとめて引用符で囲みます。
=COUNTIFS(E2:E11,">10000")売上が10,000円を超える行は6件です。演算子の種類と意味は、Excelの比較演算子の使い方にまとめています。
空欄・0以外の条件でCOUNTIFSの件数がずれる原因
空欄や「0以外」を条件にすると、エラーは出ないのに件数だけがずれます。式は正しそうに見えるので、気づきにくいのが厄介なところ。
原因③：空欄条件の3表記（"" / "=" / ""）を取り違えている
空欄を表す条件には ""・"="・"<>" の3つがあり、数える対象が少しずつ違います。サンプルのC列（空白セル3件・="" セル3件・文字ありセル4件）で比べると、次のとおりです。
| 条件 | 空白セル | ="" セル | 文字ありセル | COUNTIFS | SUMIFS（売上） |
|---|---|---|---|---|---|
"" | 数える | 数える | 数えない | 6 | 73,000 |
"=" | 数える | 数えない | 数えない | 3 | 35,000 |
"<>" | 数えない | 数える | 数える | 7 | 80,000 |
式は =COUNTIFS(C2:C11,"") のように、条件の部分だけを入れ替えています。SUMIFSは =SUMIFS(E2:E11,C2:C11,"") の形です。
ポイントは ="" セルの扱い。見た目は空欄でも、"" では空欄として、"<>" では「何か入っているセル」として数えられます。IF関数やVLOOKUPで空白を返している列は、この ="" セルだらけになりがちです。
目的別に、誤式と正式を整理しておきましょう。
| やりたいこと | 誤式（戻り値） | 正式（戻り値） |
|---|---|---|
| 何も入力していない行だけ数える | =COUNTIFS(C2:C11,"")（6） | =COUNTIFS(C2:C11,"=")（3） |
| 見た目が空欄の行をすべて数える | =COUNTIFS(C2:C11,"=")（3） | =COUNTIFS(C2:C11,"")（6） |
| 文字が書いてある行だけ数える | =COUNTIFS(C2:C11,"<>")（7） | =SUMPRODUCT(--(LEN(C2:C11)>0))（4） |
3つ目は、文字数が1以上のセルだけを数える書き方です。売上を合計するなら、次の式で 42,000 になります。
=SUMPRODUCT((LEN(C2:C11)>0)*E2:E11)なお、3表記の違いはMicrosoftのサポートページに明記がありません。自分のファイルで使う前に、上の式で件数を一度確かめておくと安心です。単一条件での空欄の数え方は、COUNTIF関数の使い方も参考になります。
原因④："0" が空白セルまで数えている
「値引きがある行」を数えるつもりで "<>0" と書くと、空白セルまで数えられます。"<>0" の意味は「0と等しくない」。空白セルは0と等しくないので、条件に当てはまってしまうわけです。
サンプルのD列は、0が3件・空白が3件・値引きありが4件。数えたいのは4件です。
| 区分 | 式 | 戻り値 |
|---|---|---|
| 誤式 | =COUNTIFS(D2:D11,"<>0") | 7 |
| 正式（空白を除く条件を追加） | =COUNTIFS(D2:D11,"<>",D2:D11,"<>0") | 4 |
| 正式（マイナスがない列なら） | =COUNTIFS(D2:D11,">0") | 4 |
ちなみに =COUNTIFS(D2:D11,0) は3件で、空白は数えません。「=0 は3件、<>0 は7件」で、合計すると全10行になる関係です。
SUMIFSでも、合計する列が別なら影響が出ます。値引きのある行の売上を合計すると、差は次のとおりです。
| 区分 | 式 | 戻り値 |
|---|---|---|
| 誤式 | =SUMIFS(E2:E11,D2:D11,"<>0") | 90,000 |
| 正式 | =SUMIFS(E2:E11,D2:D11,"<>",D2:D11,"<>0") | 52,000 |
一方、値引き額そのものを =SUMIFS(D2:D11,D2:D11,"<>0") で合計すると、どちらでも 2,000 です。空白は0として足されるだけなので、結果が変わりません。SUMIFSで気づきにくいのは、このためです。
もう1つ注意したいのが、D列が数式で ="" を返している場合。"<>" は ="" セルを除けないため、マイナスの値がない列なら ">0" で書くほうが確実です。
なお、"<>0" が空白を数える点も含め、ここで紹介した挙動はサポートページに明記がありません。手元のデータで件数を確かめてください。
範囲・文字数・桁数の制限でSUMIFS・COUNTIFSが0や#VALUE!になる原因
最後は、範囲の指定や条件の長さが原因になるケースです。エラーが出るものと、エラーなしで間違った値を返すものがあります。
原因⑤：条件範囲と合計範囲のサイズがずれている
SUMIFSでは、条件範囲と合計範囲の行数・列数をそろえる必要があります。Microsoftのサポートページでも案内されているルールです。サイズが違えば、結果はエラー（通常は #VALUE!）。
もっと怖いのは、サイズは同じでも位置が1行ずれているケース。この場合はエラーにならず、ずれた行の売上が足されてしまいます。
| 区分 | 式 | 戻り値 |
|---|---|---|
| 誤式（行数が違う） | =SUMIFS(E2:E11,B2:B10,"佐藤") | #VALUE! |
| 誤式（1行ずれ） | =SUMIFS(E3:E12,B2:B11,"佐藤") | 38,000 |
| 正式 | =SUMIFS(E2:E11,B2:B11,"佐藤") | 43,000 |
1行ずれた式では、B2 の佐藤に E3 の 8,000 が対応し、以下も1行ずつずれて足されます。佐藤の本当の合計は 43,000 円なので、差は 5,000 円。
SUMIFは、ここの動きがSUMIFSと違います。合計範囲のサイズが条件範囲と違っても、エラーになりません。合計範囲の左上のセルを起点に、条件範囲と同じ大きさへ広げて計算するんです。
=SUMIF(B2:B11,"佐藤",E2)この式は E2:E11 として計算され、43,000 を返します。エラーにならないぶん、E3 と書けば E3:E12 として 38,000 を返し、ずれに気づけません。SUMIFの引数の順番は、SUMIF関数の使い方で確認できます。
行を追加したら、すべての範囲を一緒に広げるのが基本です。表をテーブル（Ctrl+T）に変換して列名で参照すると、範囲のずれ自体が起きにくくなるのでおすすめ。
原因⑥：条件が255文字を超えている／閉じたブックを参照している
長い文章をそのまま条件にすると、結果が正しく出ないことがあります。Microsoftのサポートページでは、255文字を超える文字列を照合すると「間違った結果が返される」とされています。エラーが出るとは限らないので、長い条件で結果がおかしいときは疑ってみてください。
サンプル表には長い文章がないため、式の形だけ示します（J2 に300文字の文章が入っている想定）。
| 区分 | 式 | 結果の違い |
|---|---|---|
| 誤式 | =COUNTIFS(C2:C11,J2) | 正しく数えられないことがある |
| 回避策（公式が示す方法） | =COUNTIFS(C2:C11,"前半の文字列"&"後半の文字列") | 文字列を & で分けて渡す |
| 正式（直接比較） | =SUMPRODUCT(--(C2:C11=J2)) | 条件の長さに左右されにくい |
公式が示す回避策は、長い文字列を & で分割してつなぐ方法です。ただ、件数が正しく出るかは手元で一度確かめてください。セルの文章をそのまま比べたいなら、SUMPRODUCTで直接比較するほうが確実でしょう。
もう1つ、別のブックを参照しているときの #VALUE! も覚えておきましょう。COUNTIFが別ブックを参照し、そのブックが閉じた状態で再計算されると #VALUE! になります。これはサポートページにも載っている症状です。
=COUNTIF([集計元.xlsx]Sheet1!$C$2:$C$11,"至急")解決策は、参照先のブックを開いてから再計算すること。範囲を引数にとるSUMIFS・COUNTIFSでも、同じ症状になる場合があります。参照先を開けない運用なら、SUMPRODUCTで書き換える方法も検討してみてください。
原因⑦：16桁以上の番号が別の番号と一致してしまう
会員番号やカード番号のような16桁以上の番号を条件にすると、違う番号まで一致扱いになることがあります。サンプルの2行目と3行目は、末尾の1桁だけが違う番号です。
| 区分 | 式 | 戻り値 |
|---|---|---|
| 誤式 | =COUNTIFS(F2:F11,F3) | 2 |
| 正式 | =SUMPRODUCT(--(F2:F11=F3)) | 1 |
| 誤式 | =SUMIFS(E2:E11,F2:F11,F3) | 20,000 |
| 正式 | =SUMPRODUCT((F2:F11=F3)*E2:E11) | 8,000 |
COUNTIFSは …456 と …457 を同じ番号とみなし、結果は2件。SUMIFSも2行目の 12,000 を足してしまい、本来の 8,000 より多くなるんです。
背景には、Excel が数値として扱える有効桁が15桁までという仕様があります。F列を文字列で入力していても、比較の過程で16桁目以降の違いが反映されないと考えられます。なお、この挙動はMicrosoftのサポートページには明記されていません。
回避するには、COUNTIFS・SUMIFSを使わず、SUMPRODUCTで直接比べます。ちょっとむずかしく見えますが、やっていることはシンプルです。
F2:F11=F3で、1行ずつ「同じかどうか」を TRUE / FALSE で判定する--で TRUE を1、FALSE を0に変えて、SUMPRODUCTで足す- 売上を合計するときは、判定結果に売上の列を掛ける
番号の重複チェックにCOUNTIFを使っている場合も、同じ落とし穴があります。SUMPRODUCTとの使い分けは、SUMIF・SUMIFS・SUMPRODUCTの違いで比較しています。
よくある質問とまとめ
最後に、SUMIFS・COUNTIFSの条件が効かないときによく出る疑問を3つ見ていきましょう。
Q. SUMIFSで「佐藤または鈴木」のようなOR条件は書けますか？
SUMIFSに並べた条件は、すべて「かつ（AND）」で判定されます。OR条件にしたいときは、SUMIFSを担当者ごとに書いて足し算するのが手軽です。
=SUMIFS(E2:E11,B2:B11,"佐藤")+SUMIFS(E2:E11,B2:B11,"鈴木")サンプルでは 43,000＋41,000 で 84,000 になります。条件が増えて式が長くなるなら、SUMPRODUCTに切り替えるのも手でしょう。
Q. 「>=」などの演算子もセルで切り替えたいときは？
演算子と値を別々のセルに入れて、& でつなげば切り替えられます。G5 に >=、H5 に 10000 を入れた場合は次の式です。
=COUNTIFS(E2:E11,G5&H5)サンプルでは、売上が10,000円以上の6件を返します。1つのセルに >=10000 とまとめて入れ、条件にそのセルを指定する方法でもかまいません。
Q. 日付が数値か文字列か、まとめて確認する方法は？
=COUNT(A2:A11) を入れてみてください。COUNTは数値のセルだけを数えるので、10と表示されれば全行が日付（数値）です。
10より少なければ、文字列の日付が混ざっています。その場合は、条件式ではなくデータ側を直す必要があります。
まとめ
ExcelのSUMIFS・COUNTIFSで条件が効かない原因と直し方を、もう一度整理しましょう。
- 原因①：日付は
">="&H2のように & でつなぎ、終了日は"<"&H3+1（翌日未満）で指定する - 原因②：演算子は引用符の内側、セル番地は外側。全角の
＞＝に注意する - 原因③：未入力だけなら
"="、見た目が空欄なら""。"<>"は=""セルも数える - 原因④：
"<>0"は空白も数えるので、"<>"を追加するか">0"を使う - 原因⑤：範囲の行数と開始位置をそろえる。SUMIFはずれても黙って計算する
- 原因⑥：255文字超の条件は & で分割。別ブック参照は参照先を開く
- 原因⑦：16桁以上の番号は SUMPRODUCT で直接比較する
単一条件のSUMIF・COUNTIFでも、条件の書き方のルールは同じです。復習にはSUMIF関数の使い方とCOUNTIF関数の使い方が役立ちます。Googleスプレッドシートを使っている人は、スプレッドシートでSUMIF・COUNTIFが0になる原因も見てみてください。
式を直しても結果が合わないときは、データそのものに原因があるかもしれません。Excel集計・検索が合わない原因5つと直し方で、文字列数値や見えない空白をチェックしてみましょう。
