ExcelのSUMIFが0になるときの直し方｜条件・数値・範囲の3点を確認
ExcelのSUMIFが0になる・合計されないときの確認順
ExcelのSUMIFが0になる、または一部しか合計されないときは、「条件」「数値」「範囲」を分けて調べます。
該当額がすべて0、または正負の金額が相殺された0は正しい結果です。
条件はCOUNTIFで一致件数を確かめ、金額はISNUMBERで数値かを確認。最後に条件範囲と合計範囲の開始行・終了行をそろえます。
いきなり元データを上書きせず、シートのコピーなどを残してから進めてください。
| 確認する点 | 使う式・見る箇所 | この例の正常値 |
|---|---|---|
| 条件 | =COUNTIF(A2:A4,D2) |
2 |
| 数値 | =ISNUMBER(B2) |
TRUE |
| 範囲 | A2:A4とB2:B4の行 | ともに2〜4行目 |
これは確認順を示す説明用の図解です。実際のExcel画面ではなく、架空データを使っています。
SUMIFが0または一部だけになる原因を、同じ3行の表で条件・数値・範囲の順に切り分けます。
まず3行の表で正しい合計を確認する
ここからは、学習用の架空データを使います。結果はMicrosoftが示すSUMIFの仕様に基づく期待値です。
| セル | A列：区分 | B列：金額 |
|---|---|---|
| 2行目 | 文具 | 1200 |
| 3行目 | 飲料 | 800 |
| 4行目 | 文具 | 500 |
D1に「集計する区分」、D2に「文具」と入れます。基本式は次のとおりです。
=SUMIF(A2:A4,D2,B2:B4)
引数は「条件を調べるA列」「条件セルD2」「合計するB列」の順です。文具の1200と500を足すため、結果は1700です。
条件を式に直接書くなら、=SUMIF(A2:A4,"文具",B2:B4)でも結果は1700です。D2のようなセル参照には引用符を付けません。
1. 条件の文字とセル参照を確認する
まず空きセルに=COUNTIF(A2:A4,D2)と入れます。COUNTIFは条件に合うセル数を返す関数です。
この例では「文具」が2件なので、COUNTIFの結果は2です。0ならD2の文字とA列を見比べ、想定より少なければ一部の表記や参照漏れを確認します。
=SUMIF(A2:A4,"D2",B2:B4)とすると、D2の内容ではなく「D2」という文字を探します。この表には該当せず、結果は0です。
A2の「文具」の末尾に半角空白がある変形例では、D2と一致するのはA4だけです。COUNTIFは1、SUMIFは500になります。
少数のセルなら数式バーで前後の不要な空白を確認し、元のラベルの意味を変えずに直します。ワイルドカードの*でごまかさないでください。
TRIMを使うなら、補助列で=TRIM(A2)とし、結果を見てから採用します。TRIMはすべての空白文字を除けるわけではありません。
COUNTIFが2でも、金額が数値とは限りません。件数が正しければ、次にB列の中身を調べます。
2. 金額が数値かISNUMBERで確認する
空きセルに=ISNUMBER(B2)と入れます。数値の1200ならTRUE、文字列として保存された1200ならFALSEです。
ISNUMBERは数値かどうかを判定するだけで、文字列の数字を自動変換しません。FALSEの場合は、文字列のほかに空白やエラーでないかも見ます。
これは保存された型の違いを示す説明用の図解です。実際のExcel画面ではなく、架空データを使っています。
B2の1200だけが文字列の変形例では、条件は2件でも、加算されるのは数値の500だけです。SUMIFの結果は500になります。
B2とB4がどちらも文字列なら、加算できる文具の金額がなく、結果は0です。表示形式を変えただけで終えず、ISNUMBERで再確認します。
元の列を残してVALUEで直す
空いているC列を補助列として使えば、B列をすぐに上書きせずに確認できます。
VALUEは合計に使う金額列だけに使います。商品番号や郵便番号など、先頭の0を残す識別子は数値に変換しません。
- C1に「変換後金額」、C2に
=VALUE(B2)と入れ、C4までコピーする。 - C2:C4が1200、800、500となり、
=ISNUMBER(C2)がTRUEになることを確かめる。 =SUMIF(A2:A4,D2,C2:C4)の結果が1700になることを確かめる。- B列へ戻す必要がある場合だけ、C2:C4をコピーし、B2へ「値」の貼り付けを行う。
Microsoftの文字列数値の変換手順では、警告メニューから数値へ変換する方法と、VALUEを使う方法が示されています。
警告メニューの名称や配置は環境で異なることがあります。警告がないときは、補助列でVALUEを使えます。
VALUEが#VALUE!になるセルは、単位や不要な文字が含まれていないかを確認します。IFERRORで0に隠さず、元の表記を見直しましょう。
3. 条件範囲と合計範囲の行をそろえる
基本例の条件範囲はA2:A4、合計範囲はB2:B4です。どちらも2行目から4行目までの3行で、A2とB2のように同じ行同士が対応します。
これは条件列と合計列の対応を示す説明用の図解です。実際のExcel画面ではなく、架空データを使っています。
=SUMIF(A2:A3,D2,B2:B3)では4行目が両方の範囲から抜けるため、500が入らず、結果は1200です。必要な最終行まで両方の範囲を伸ばします。
=SUMIF(A2:A4,D2,B3:B5)のように開始行がずれると、A2にB3、A3にB4という意図しない対応になります。
条件範囲と合計範囲の大きさが違っても、必ずエラーで止まるとは限りません。数字が表示されていても、開始行と終了行を見てください。
合計範囲を省略していないか
SUMIFの構文はSUMIF(範囲,検索条件,[合計範囲])です。第三引数を省くと、第一引数の範囲そのものが合計対象になります。
=SUMIF(A2:A4,D2)では、区分名が入ったA列を合計しようとするため、この例の結果は0です。金額列のB2:B4を第三引数に加えます。
ただし、合計範囲の省略自体が常に間違いではありません。=SUMIF(B2:B4,">=1000")のように同じ列を条件判定して足す式では、基本例の結果は1200です。
直したら1700と一致件数をもう一度確認
修復後は、実際に合計する列に合わせて、次の4点を順に再確認します。
COUNTIF(A2:A4,D2)が2- C列を使う場合はC2・C4、B列へ値を戻した場合はB2・B4のISNUMBERがTRUE
- 条件範囲A2:A4と、使用する合計範囲C2:C4またはB2:B4が、ともに2〜4行目
- C列を使う場合は
=SUMIF(A2:A4,D2,C2:C4)が1700。B列へ値を戻した場合は=SUMIF(A2:A4,D2,B2:B4)が1700
1700はこの架空例だけの期待値です。自分の表では、該当行の金額を手計算した値とSUMIFの結果を比べます。
まだ合わなければ、元表から3行程度をコピーした小さな確認表で、条件と金額の中身を見比べてください。
なお、Microsoft公式のSUMIFページにはMicrosoft 365、Excel 2024、Excel 2021、Excel 2019、Excel 2016などが対象として記載されています。
本文はそれらに共通する関数仕様の確認手順です。
まとめ
SUMIFが0になるときは、すぐに式全体を作り直すより、条件・数値・範囲を1つずつ切り分けると追いやすくなります。
まずCOUNTIFで一致件数を見て、次にISNUMBERで金額の型を確認。文字列の数値は元列を残し、VALUEを入れた補助列で変換します。
最後に条件範囲と合計範囲の行をそろえ、第三引数の省略が意図どおりかを確かめてください。
元データを残したうえで、3行の小さな例から同じ順番で調べてみましょう。
