【Excel】エクセルSUMIFとSUMIFSの違いは？使い分けと計算式を解説
Excelで条件に合う数値だけを集計したい場面では、SUMIF関数とSUMIFS関数が役立ちます。
似た名前の関数ですが、指定できる条件の数、数式の並び順、エラーになりやすいポイントには明確な違いがあります。
たとえば売上表から特定の担当者の売上を求める、商品と月の両方が一致する金額を集計するといった処理では、条件に応じた選択が重要です。
この記事では、サンプルデータを使いながら、SUMIFとSUMIFSを迷わず使い分けるための考え方と具体的な計算式を確認していきます。
SUMIFは条件が1つの合計に便利です。
SUMIFSは条件が2つ以上の合計に便利です。
両者は合計範囲と条件範囲の指定順が異なるため、数式をそのまま置き換えることはできません。
集計表を作るときは、元データの1行目を見出し行として、2行目以降に明細が入力されている状態を想定します。
SUMIFとSUMIFSの使い分け
| A | B | C | D |
|---|---|---|---|
| 担当者 | 商品 | 月 | 売上 |
| 田中 | A商品 | 4月 | 12000 |
| 佐藤 | B商品 | 4月 | 8500 |
それではまずSUMIFとSUMIFSの使い分けについて解説していきます。
結論として、条件が1つならSUMIF、複数条件をすべて満たす金額を求めるならSUMIFSを選ぶと分かりやすいです。
条件数による選択
SUMIFは、指定した範囲から1つの条件に一致するデータを探し、その行に対応する数値を合計する関数です。
たとえば担当者が田中である売上だけを合計する場合、条件は担当者名だけなのでSUMIFが適しています。
=SUMIF(A2:A10,”田中”,D2:D10)
この数式では、A列で田中を検索し、一致した行のD列の売上を加算します。
条件が明確に1つだけであれば、短いSUMIFの数式は読みやすさにもつながります。
一方、担当者が田中で、さらに商品がA商品である売上を調べる場合は、SUMIFだけでは条件を追加できません。
複数条件に対応するSUMIFS
SUMIFSは、複数の条件範囲と条件を指定できる合計関数です。
田中かつA商品というように、複数の条件を同時に満たしたレコードだけを合計したいときに使用します。
=SUMIFS(D2:D10,A2:A10,”田中”,B2:B10,”A商品”)
この式は、D列を合計範囲にし、A列が田中、かつB列がA商品の行だけを判定します。
複数の条件はすべて満たす必要があり、基本的にはAND条件として機能します。
条件を増やしても合計範囲は最初に1回だけ指定する点を押さえると、数式の構造を理解しやすくなります。
集計目的による判断
日報で担当者別の売上を出す、カテゴリ別の数量を求めるといった単純な集計ならSUMIFで十分です。
月別かつ担当者別、商品別かつ地域別のように分析軸を重ねる場合は、最初からSUMIFSを使うと後から条件を増やしやすくなります。
ただし、条件が1つしかないからといってSUMIFSを使えないわけではありません。
将来的に条件を追加する予定がある表では、SUMIFSで統一する選択もあります。
とはいえ、数式を読む人が多い共有ファイルでは、目的に合った関数を選ぶほうが保守しやすいでしょう。
【操作のポイント】条件が1つならSUMIF、2つ以上の絞り込みならSUMIFSという基準で、まず関数を選びます。
SUMIF関数の計算式
| A | B | C |
|---|---|---|
| 担当者 | 売上 | 集計条件 |
| 田中 | 12000 | 田中 |
| 鈴木 | 9800 | 合計 |
続いてはSUMIF関数の計算式を確認していきます。
SUMIFの基本構文は、条件を探す範囲、条件、合計する範囲の順です。
基本構文と引数
=SUMIF(範囲,条件,合計範囲)
最初の範囲には、条件を判定するセル範囲を指定します。
条件には、田中のような文字列、10000以上のような比較条件、または条件が入力されたセルを指定できます。
最後の合計範囲には、実際に足し算したい数値のセル範囲を指定します。
範囲と合計範囲は、同じ行数と列数でそろえることが重要です。
たとえばA2からA10を条件範囲にしたなら、合計範囲はD2からD10のように、同じ9行にそろえましょう。
セル参照を使う数式
条件を数式内へ直接入力すると、別の担当者を調べるたびに式を書き換える必要があります。
そこで、F2セルに担当者名を入力し、そのセルを条件として参照する方法が便利です。
=SUMIF($A$2:$A$10,F2,$D$2:$D$10)
この式を下方向へコピーすれば、F列の担当者名に応じた売上を並べて集計できます。
データ範囲にドル記号を付けると、オートフィルで数式をコピーしても元データの範囲がずれません。
条件セルだけを相対参照に残すと、担当者別集計表を効率よく作成できます。
数値条件とワイルドカード
SUMIFでは、売上が10000以上のデータだけを合計することも可能です。
=SUMIF(D2:D10,”>=10000″,D2:D10)
比較演算子を使う場合は、演算子と数値を二重引用符で囲みます。
また、文字列の一部を条件にする場合は、アスタリスクを任意の文字列として使えます。
たとえば田で始まる担当者を対象にするなら、条件は田*です。
ただし、似た名前まで合計される可能性があるため、氏名や商品コードの判定には完全一致を基本にするほうが安全です。
【操作のポイント】条件を変更しながら集計する表では、条件をセルに入力し、そのセルをSUMIFで参照します。
SUMIFS関数の計算式
| A | B | C | D |
|---|---|---|---|
| 担当者 | 商品 | 月 | 売上 |
| 田中 | A商品 | 4月 | 12000 |
| 田中 | B商品 | 4月 | 6400 |
続いてはSUMIFS関数の計算式を確認していきます。
SUMIFSは合計範囲を最初に指定し、その後に条件範囲と条件をセットで並べます。
引数の並び順
=SUMIFS(合計範囲,条件範囲1,条件1,条件範囲2,条件2)
SUMIFでは条件範囲が最初でしたが、SUMIFSでは合計範囲が最初です。
この違いが、SUMIFからSUMIFSへ変更したときに最も起こりやすい入力ミスです。
SUMIFSは最初に足し算する列を選ぶと覚えると、引数を逆にしにくくなります。
その後で、どの列をどの条件で絞るかを、条件範囲と条件の組み合わせで追加していきます。
担当者と商品の集計例
=SUMIFS($D$2:$D$10,$A$2:$A$10,F2,$B$2:$B$10,G2)
この式では、F2に担当者名、G2に商品名を入力し、両方に一致する売上を集計します。
たとえばF2が田中、G2がA商品なら、田中がA商品を販売した売上だけが結果になります。
数式を入力したセルの右下にあるフィルハンドルを下方向へドラッグすれば、次の条件行にも式をコピーできます。
元データの範囲は絶対参照にしておくと、コピー後も集計対象が変わりません。
日付範囲を条件にする集計
SUMIFSは、開始日以上かつ終了日未満という2つの条件を使い、月別や期間別の売上を集計する用途にも向いています。
=SUMIFS(D2:D100,C2:C100,”>=”&F2,C2:C100,”<“&G2)
F2に開始日、G2に翌月1日などの終了基準日を入力すると、指定した期間だけを正確に合計できます。
日付は表示形式ではなくExcel内部のシリアル値で比較されるため、条件セルには文字列ではなく正しい日付を入力しましょう。
終了条件を翌月1日未満にすると、時刻を含むデータでも月末日の漏れを防ぎやすくなります。
【操作のポイント】SUMIFSでは合計範囲を先頭に置き、条件範囲と条件を必ずペアで追加します。
条件指定とセル参照
| F | G | H |
|---|---|---|
| 担当者 | 商品 | 集計結果 |
| 田中 | A商品 | 12000 |
続いては条件指定とセル参照を確認していきます。
条件をセルで管理すると、数式を編集せずに集計結果を切り替えられます。
文字列条件の扱い
条件を数式に直接書く場合、文字列は二重引用符で囲みます。
セル参照を使う場合は、F2のようにセル番地だけを記述するため、二重引用符は不要です。
数式内の文字とセルに入力された文字は、余分な空白があると一致しないことがあります。
元データに見えない空白が混じる場合は、TRIM関数や置換機能で整形してから集計するのが有効です。
比較演算子と連結
セルの値を使って10000以上のような条件を作るときは、演算子とセル参照をアンパサンドで連結します。
=SUMIF(D2:D10,”>=”&F2,D2:D10)
F2が10000なら、この式は10000以上の売上を合計します。
演算子だけを二重引用符で囲み、セル番地は引用符の外に書く点がポイントです。
比較条件をセル参照にすると、基準値を変えるだけで分析結果を更新できます。
空白セルとエラーの確認
条件セルが空白の場合、SUMIFやSUMIFSは空白のデータを対象にしてしまうことがあります。
意図しない集計を防ぐには、条件セルが空白なら結果も空白にするIF関数との組み合わせが便利です。
=IF(F2=””,””,SUMIF($A$2:$A$10,F2,$D$2:$D$10))
また、条件範囲と合計範囲の大きさが異なると、想定外の値になったりエラーになったりする場合があります。
データを追加した後は、数式の末尾行まで集計範囲に含まれているか確認しましょう。
【操作のポイント】集計条件は専用セルに置き、空白時の表示とデータ範囲の行数をあわせて確認します。
SUMIFとSUMIFSのエラー対処
| 確認項目 | 主な原因 | 対処 |
|---|---|---|
| 結果が0 | 表記の違い | 空白や全角半角を確認 |
| エラー表示 | 範囲サイズの不一致 | 開始行と終了行をそろえる |
続いてはSUMIFとSUMIFSのエラー対処を確認していきます。
数式の形が正しく見えても、データの種類や範囲のずれによって結果が合わないことがあります。
結果がゼロになる原因
結果が0になる場合は、まず条件の文字列が元データと完全に一致しているか確認します。
田中と田中の後ろに空白がある文字列は、見た目が同じでもExcelでは別の値です。
数字が文字列として保存されていると、数値条件に一致しないこともあります。
表示上の見た目ではなく、セルの実際の値とデータ型を確認することが解決の近道です。
合計範囲のずれ
SUMIFでは条件範囲と合計範囲の先頭行をそろえる必要があります。
たとえばA2からA10を条件範囲にして、D3からD11を合計範囲にすると、行が1つずれて別の売上を集計します。
SUMIFSでも、すべての条件範囲と合計範囲を同じ大きさに指定することが基本です。
範囲を選択する際は、1行目の見出しを含めないように注意しましょう。
複数条件の判定ミス
SUMIFSの複数条件は、いずれかではなくすべてに一致するデータを対象にします。
田中または佐藤のようなOR条件を求める場合は、それぞれのSUMIFSを足し合わせる方法があります。
=SUMIFS(D2:D10,A2:A10,”田中”)+SUMIFS(D2:D10,A2:A10,”佐藤”)
同じデータが重複して数えられない条件かを確認したうえで利用しましょう。
SUMIFSはAND条件の関数であると理解すると、期待と違う集計結果を見直しやすくなります。
【操作のポイント】結果が合わないときは、条件の表記、データ型、各範囲の開始行と終了行の順で確認します。
まとめ エクセルSUMIFSとSUMIFの違いと使い分け
SUMIFとSUMIFSの違いは、指定できる条件の数と数式の引数の並び順にあります。
条件が1つの売上集計や件数に応じた合計にはSUMIFが使いやすく、複数の条件を重ねた集計にはSUMIFSが適しています。
SUMIFは条件範囲、条件、合計範囲の順に入力します。
SUMIFSは合計範囲を最初に指定し、その後に条件範囲と条件をペアで追加します。
特にSUMIFSの先頭は合計範囲である点を覚えておけば、数式入力のミスを大きく減らせます。
条件をセル参照にすれば、担当者名、商品名、期間、金額の基準を変えるだけで集計表を再利用できます。
また、範囲の行数をそろえ、文字列の空白や数値のデータ型を確認することも正確な集計には欠かせません。
SUMIFとSUMIFSを使い分けて、売上管理、在庫管理、経費集計などのExcel作業を効率化していきましょう。
