サービス
サービスを探す
プロ人材を探す
仕事を探す
ブログを探す
SUMIFS/COUNTIFSが0を返すときに確認する5か所
記事IT・テクノロジー
集計行を作って条件を指定したのに、SUMIFSが0を返す。データはちゃんとあるし、
目で見ても条件に合う行がある。それなのに0のまま変わらない。
これはエラーが出ないぶん厄介です。数式自体は動いているので、
「条件の書き方が間違っている」のか「データ側に問題がある」のか切り分けにくい。
0を返す原因は、だいたい次の5か所のどれかです。順番に、原因から見ていきます。
■ 確認1：条件列の値に余分な空白が入っている
「東京」で絞り込んでいるのに、実際のセルには「東京 」のように末尾へ半角スペースが
1つ付いていることがあります。見た目はまったく同じ「東京」に見えますが、
SUMIFS/COUNTIFSは完全一致で比較するため、この空白1つで不一致になります。
他システムから貼り付けたデータや、フォームの自由記入欄から取り込んだ値で
よく起こります。疑わしい列があれば、次の数式で空白混入を確認できます。
=LEN(A2)-LEN(TRIM(A2))
0以外の数字が返れば、余分な空白が入っています。対処は、条件列に
TRIM関数をかけた列を作り、その列を条件範囲として使い直すことです。
■ 確認2：条件列の数値が文字列として入っている
金額や数量を条件にしている場合、">=100"のような比較条件は、対象セルが
本物の数値でないと機能しません。 見た目が数字でも、セルが左揃えなら文字列です。
文字列のまま比較条件">="を当てても大小比較ができず、該当なしとして
0が返ります。エラーにならないので、条件の書き方を疑って延々と直しても
解決しないのがこのパターンです。
直すには、対象列を選んで「区切り位置」を実行するか、次の数式で数値化した列を作り、
そちらを条件範囲に使います。
=VALUE(A2)
■ 確認3：日付条件が文字列として渡されている
期間集計でよく起きるのが、次のような書き方です。
=SUMIFS(C:C,A:A,"東京",B:B,">=2026/1/1")
これは動きそうに見えますが、"2026/1/1"という文字列がそのまま比較に使われるため、
環境や設定によって日付として解釈されず、意図と違う比較になることがあります。
安全なのは、DATE関数で組み立ててから文字列連結する書き方です。
=SUMIFS(C:C,A:A,"東京",B:B,">="&DATE(2026,1,1))
こちらは日付をシリアル値として渡すため、比較が確実に効きます。
B列側がそもそも文字列の日付になっている場合は、この書き方に直しても0のままなので、
その場合は日付列自体を先にDATEVALUEなどで数値化してください。
この症状はExcelとGoogleスプレッドシートで出方が違います。 Excelは
ロケール（地域設定）に沿ってかなり柔軟に文字列を日付として解釈しますが、
Googleスプレッドシートはスプレッドシートの「ファイルの設定」で決めた
ロケールに厳密に従うため、同じ"2026/1/1"という文字列でも、設定が
米国形式（月/日/年）になっていると1月1日ではなく別の日付として解釈され、
条件に一致せず0を返すことがあります。両方の環境で使うファイルは、
文字列の日付条件に頼らずDATE関数で組み立てる書き方に統一してください。
■ 確認4：条件範囲と合計範囲がずれている
SUMIFS/COUNTIFSは、条件範囲と合計範囲（またはカウント対象範囲）の行数・列数が
そろっていることを前提に動きます。本来サイズが一致していないとエラーに
なるはずですが、A:AやC:Cのように列全体を指定していると、サイズは常に
一致しているとExcel側は判断するため、エラーが出ないまま実質的に
違う行同士を比較する結果になっていることがあります。
複数条件の数式を別の集計行にコピーしたとき、相対参照のまま貼り付けると、
条件範囲や合計範囲がもとの表からずれてしまうことがあります。
列全体（A:AやC:C）を指定していると、多少ずれても同じ列を指しているように
見えてしまい、ずれに気づきにくいのが厄介なところです。数式をコピーした後は
$を付けて絶対参照にするか、一度セルを選択して実際にどの範囲を見ているか
確認してください。
■ 確認5：全角・半角や表記の粒度が条件と一致していない
SUMIFS/COUNTIFSは大文字・小文字は区別しませんが、全角と半角は別の文字として
区別します。 条件に半角の"Tokyo"を指定していても、データ側が全角の
"Ｔｏｋｙｏ"であれば一致しません。「東京都」と「東京」のような表記の
粒度違いも、完全一致では引っかかりません。
部分一致で拾いたい場合は、ワイルドカードを使います。
=COUNTIFS(A:A,"*東京*")
逆に、商品名に本物のアスタリスク(*)や疑問符(?)が含まれていて、
それを普通の文字として検索したい場合は、記号の前にチルダ(~)を付けて
エスケープしないと、ワイルドカードとして解釈されてしまいます。
=COUNTIFS(A:A,"A~*B")
■ やってはいけないこと
0が返るからといって、条件に合う行を手で数えて合計欄に直接入力すること。
翌月また同じ集計をするときに同じ作業をやり直す羽目になり、しかも
今回0になった原因はデータに残ったままなので、次も同じ場所でつまずきます。
条件式そのものを疑って何度も書き直すのも同様で、書き方は合っていて
データ側に原因があるケースの方が実際には多いです。
■ それでも解決しないとき
・5か所とも確認したのに、まだ0のまま
・条件が5個以上あって、どこが効いていないのか切り分けられない
・複数シートをまたいで条件を組んでいて、参照が合っているか自信が持てない
このあたりまで来たら、データを見ながら1つずつ数式で検証した方が早いです。
私はExcel・スプレッドシートの集計まわりの調査と修正を承っています。
どの条件が効いていなかったかを切り分けた形でお返ししています。
――
この記事の執筆には生成AIを活用しています。内容は実際の挙動を確認して書いています。
このブログを見た人にオススメ
