FORMULA WORKBENCH / TROUBLESHOOTING
SUMIFSが0になるとき、文字列・空白・範囲を順に調べる
条件に合う行があるつもりなのにSUMIFSの結果が0になるときは、式を一度に書き換えず、①条件一致、②文字列の中身、③合計セルの型、④範囲の形の順に確かめます。
更新:2026年9月25日。下表は説明用の架空データです。
確認用の小さな入力例
次のデータをA1から入力します。7行目の金額は、セルへアポストロフィを付けて '6500 と入力した「数値に見える文字列」の例です。
| 行 | A:部署 | B:状態 | C:金額 |
|---|---|---|---|
| 2 | 営業 | 入金済 | 12,000(数値) |
| 3 | 営業 | 入金済 | 8,000(数値) |
| 4 | 営業␠(末尾に半角空白) | 入金済 | 5,000(数値) |
| 5 | 営業 | 未入金 | 9,000(数値) |
| 6 | 営業 | 入金済 | 空欄 |
| 7 | 営業 | 入金済 | 6,500(文字列) |
部署列の ␠ は見えにくい半角空白を示す記号です。実際に入力する値は「営業」の後ろに半角スペースを1つ付けます。
この式を入力します。
=SUMIFS(C2:C7,A2:A7,"営業",B2:B7,"入金済")
条件に一致するのは2・3・6・7行目です。数値の金額を足した期待値は 20,000 です。4行目は末尾空白で部署名が異なり、5行目は状態が異なります。6行目は空欄、7行目の金額は文字列です。
4段階で原因を絞る
- 条件に合う行があるか数える。
=COUNTIFS(A2:A7,"営業",B2:B7,"入金済")の結果は4です。0なら、まず条件値・条件範囲・文字列の空白を調べます。この COUNTIFS は条件一致行を数える確認用で、金額の数値型は見ません。 - 文字列の見えない違いを調べる。 D4へ
=LEN(A4)、E4へ=EXACT(A4,"営業")を入れると、この例では3とFALSEになります。通常の「営業」セルなら文字数は2です。前後の空白、全角/半角、表記ゆれを確認します。 - 合計セルが数値か確かめる。 D7へ
=ISNUMBER(C7)を入れると、この例ではFALSEです。数値に見える文字列は、合計範囲で数値として足されません。空欄のC6は=ISBLANK(C6)で実空白かを確認できます。 - すべての範囲の大きさと位置をそろえる。 合計範囲が
C2:C7なら、条件範囲もA2:A7、B2:B7のように同じ行数・列数で、対応するデータ行を指すようにします。見出し行だけ範囲から外れていないかも確認します。
空白と文字列を直すときの注意
- 実際に何も入っていないセル、数式が空文字を返しているセル、半角スペースだけのセルは見た目が似ていても同じ状態とは限りません。
ISBLANKとLENを組み合わせ、元データを確認してください。 - Microsoftの
TRIMは通常のASCII半角スペース向けです。改行なしスペースなど別の空白文字はTRIMだけでは取り除けない場合があります。出所を確かめずに一括置換せず、複製したデータで試してください。 - 条件に文字列を直接書く式では、
"営業"のように二重引用符で囲みます。数式の引数区切りはExcelの地域設定によって異なることがあり、本サイトのビルダーはカンマ区切りで出力します。 - 条件に一致する行があるのに結果が0なら、合計範囲が意図した金額列か、金額が数値として保存されているかを先に見ます。表示形式を「数値」に変えただけでは、文字列が数値へ変換されないことがあります。
数式を組み立てて再確認する
範囲の寸法エラーを避けるため、数式の道具箱は合計範囲と条件範囲の行数・列数が合わない場合に式の生成を止めます。データ自体の空白や数値型までは読み込みません。
SUMIFSビルダーを開くから、合計範囲 C2:C7、条件範囲 A2:A7 と B2:B7、文字列条件「営業」「入金済」を入力してください。生成後はご自身の表で同じ条件に一致する行と合計を照合します。
部署・状態・金額条件を使った別の架空例は3条件の売上集計記事にあります。
ビルダーで0件の条件式を作る
ページ上部の「数式ビルダー」を開き、「作りたい数式」で「COUNTIFS|複数条件を数える」を選びます。「条件 1」の条件範囲に A2:A7、演算子に =、条件値に 存在しない、値の種類に「文字列」を指定します。「条件 2」は条件範囲 B2:B7、演算子 =、条件値 入金済、値の種類「文字列」にします。「数式を更新する」を押すと、プレビューは次の式になります。
=COUNTIFS(A2:A7,"存在しない",B2:B7,"入金済")
この架空表のA列に「存在しない」という値はないので、式をExcelで計算すれば一致数は0になる想定です。ビルダーは入力した表を読まず、式の文字列を組み立てます。画面に表示される「0件」はビルダー内の固定サンプルの説明であり、手元の表を計算した値ではありません。Excelで確認する場合は、元データのコピーで式を試してください。
条件範囲のサイズを誤って A2:A7 と B2:B8 にすると、ビルダーは数式を出さず、範囲の行数・列数をそろえるように案内します。これは式を組み立てる前の寸法検証です。実際のセルの内容、空白、値の型までは検査しません。
制約と仕様資料
本記事は架空データの論理的な集計例です。制作時にExcelを起動して式を実行した確認ではありません。数値変換や空白除去は元データを書き換える可能性があるため、実務データのコピーで試し、重要な集計は別の方法でも照合してください。
- Microsoft:SUMIFS関数 — 引数、複数条件、範囲サイズの注意
- Microsoft:TRIM関数 — 通常の空白と削除対象外の空白文字
- Microsoft:SUM関数 — 参照範囲内のテキスト値の扱い
文字列や数値が原因と決めつけず、条件一致数・セル内容・合計範囲を一つずつ確認するのが切り分けの要点です。