Excelで複数の条件に一致するデータを合計するときに便利なのが、SUMIFS関数です。
ところが、数式を入力してみると、
「SUMIFSでエラーになる💦」
「数式は合っているように見えるのに0になる」
「複数条件を指定したら思った結果にならない」
ということがあります。
SUMIFSがうまくいかない場合、まず確認したいのが引数の順番です。
特にSUMIF関数に慣れている場合は、SUMIFSでも同じ順番で入力してしまいやすいので注意してください。
この記事では、SUMIFSでエラーになる、0になる、計算結果がおかしいときに確認したいポイントを初心者向けに解説します。
SUMIFSでエラーになるときは引数の順番を確認する
SUMIFS関数の基本的な書き方は次のとおりです。
SUMIFと同じ感覚で合計範囲を後ろに指定すると、SUMIFSでは引数の意味がずれてしまいます。
=SUMIFS(合計範囲,条件範囲1,条件1,条件範囲2,条件2)ポイントは、最初に「合計したい範囲」を指定することです。
たとえば、B列からD列に次のような表があるとします。
| 商品 | 地域 | 売上 |
|---|---|---|
| りんご | 東京 | 1000 |
| りんご | 大阪 | 1500 |
| みかん | 東京 | 800 |
| りんご | 東京 | 1200 |
「商品がりんご」かつ「地域が東京」の売上を合計したい場合は、次の数式を入力します。
=SUMIFS(D2:D5,B2:B5,"りんご",C2:C5,"東京")この場合、東京のりんごは「1000」と「1200」なので、結果は2200になります。
SUMIFとSUMIFSでは引数の順番が違う
SUMIFSで間違えやすい理由の一つが、SUMIFとSUMIFSで引数の順番が異なることです。
SUMIF関数は次のように入力します。
=SUMIF(条件範囲,条件,合計範囲)一方、SUMIFS関数は次の順番です。
=SUMIFS(合計範囲,条件範囲1,条件1,条件範囲2,条件2)違いをまとめると、次のようになります。
| 関数 | 引数の順番 |
|---|---|
| SUMIF | 条件範囲 → 条件 → 合計範囲 |
| SUMIFS | 合計範囲 → 条件範囲 → 条件 |
SUMIFでは最後に指定していた合計範囲が、SUMIFSでは最初になります。
SUMIFからSUMIFSへ数式を書き換えたときにエラーになる場合は、ここを確認してみましょう。
「SUMIFSは最初に合計範囲」と覚えておくと分かりやすいです。
SUMIFSが0になる場合に確認するポイント
SUMIFSでエラーは表示されないものの、結果が0になる場合は、条件や範囲の指定も確認してみましょう。
文字列は「” “」で囲む/余分なスペースがないか確認する
「東京」や「りんご」などの文字を条件として数式へ直接入力する場合は、「”」ダブルクォーテーションで囲みます。
=SUMIFS(D2:D5,B2:B5,"りんご")また、セル内に余分なスペースが入っている場合も注意が必要です。
たとえば、
りんごと、
りんご では、見た目が似ていても異なる文字列として扱われることがあります。
「数式は合っているのに0になる、または一部のデータが合計されない」という場合は、元データに余分なスペースが入っていないか確認してみましょう。
合計範囲と条件範囲がずれると「#VALUE!」エラーになる
SUMIFSでは、合計範囲と条件範囲の行数や列数をそろえる必要があります。
たとえば、
=SUMIFS(D2:D5,B2:B4,"りんご")では、合計範囲と条件範囲の大きさが異なっています。
そのため、次のように範囲をそろえる必要があります。
=SUMIFS(D2:D5,B2:B5,"りんご")
数式が長くなって分かりにくい場合は、各範囲の開始セルと終了セルがそろっているかを確認してみてください。
SUMIFSの複数条件は「すべて満たす」が基本
SUMIFSで複数の条件を指定すると、基本的にはすべての条件を満たしたデータが合計されます。
たとえば、
=SUMIFS(D2:D5,B2:B5,"りんご",C2:C5,"東京")なら、商品が「りんご」、地域が「東京」という2つの条件を両方満たす行だけが合計されます。
つまり、SUMIFSで条件を追加すると基本的には「AかつB」というAND条件になります。
「りんご、または、みかん」のように、どちらか一方を満たせばよいOR条件とは考え方が異なるので注意してください。
👉「AまたはB」のように、どちらか一方の条件を満たすデータを判定・集計したい場合は、こちらの記事も参考にしてください。
数式そのものがセルに表示される場合は別の原因
SUMIFSを入力したあと、計算結果ではなく、
=SUMIFS(D2:D5,B2:B5,"りんご")のように数式そのものがセルに表示される場合は、SUMIFSの引数ではなくExcelの表示設定やセルの書式が原因の可能性があります。
SUMIFS以外の数式でも同じ症状が出る場合は、SUMIFSの書き方を何度修正しても解決しません。
👉SUMIFSに限らず、数式を入力しても計算結果ではなく数式そのものが表示される場合は、こちらの記事も確認してみてください。
SUMIFSでエラーになるときの確認ポイント
SUMIFSがうまくいかない場合は、次の順番で確認すると原因を見つけやすくなります。
1.合計範囲を最初に指定しているか
2.「条件範囲 → 条件」の順番になっているか
3.合計範囲と条件範囲の大きさがそろっているか
4.文字列の条件を正しく指定しているか
5.複数条件がAND条件になることを理解しているか
6.元データに余分なスペースなどが入っていないか
特に初心者の場合は、まずSUMIFSの引数の順番から確認するのがおすすめです。
まとめ
ExcelのSUMIFSでエラーになる場合は、まず引数の順番を確認してみましょう。
SUMIFSの基本形は、
=SUMIFS(合計範囲,条件範囲1,条件1,条件範囲2,条件2)です。
特に注意したいのは、SUMIFとSUMIFSでは合計範囲を指定する位置が違うことです。
SUMIFSでエラーになる、0になる、思った結果にならない場合は、
・SUMIFSでは合計範囲を最初に指定する
・合計範囲と条件範囲の大きさをそろえる
・文字列の条件を正しく指定する
・複数条件は基本的にAND条件になる
といったポイントを一つずつ確認してみてください。
コメント