【関数】特定条件に一致する数値を合計できる「SUMIFS」
AさんAさん

商品別や日別などの条件で合計を集計したいですが、SUMだと限界あります。。
こうした場合の集計に役立つ関数はありますか?

森田森田

その場合は、関数のSUMIFSを活用すると良いですよ!
では、SUMIFSの使い方について解説していきますね。

はじめに

この記事は関数の概要とSUMの使い方を把握していることが前提です。

参考記事

関数の概要とSUMの使い方については以下の記事をご参照ください。


○○別の合計を集計する場合は「SUMIFS」が有効

実務では、データの特徴や傾向を把握するために、商品別や日別などの切り口で集計する機会は非常に多いもの。

こうした条件別の合計を集計する際、商品名や日付などの条件に一致するレコードの数値データのみで合計すれば良いですが、頑張ればSUMでも集計は可能です。

ただし、指定するセル数が多ければ多いほど、手作業は増え、誤ったセルを選択するリスクも増えてしまうため、この方法は非効率です。

こんな場合、関数の「SUMIFS」を使うことで、条件に一致するレコードのみを対象とし、瞬時に合計を集計することが可能となります。
ちなみに、SUMIFSは「サムイフズ」と呼びます。

よって、条件別の合計を集計する場合はSUMIFSを使用していきましょう。

SUMIFSの構文

SUMIFSの構文は以下の通りです。

=SUMIFS(合計対象範囲,条件範囲1,条件1,[条件範囲2,条件2],...)
特定の条件に一致する数値の合計を求めます。

引数名 必須 データ型 説明
合計対象範囲 参照 合計対象のセル範囲を指定します。
条件範囲1 参照 「条件1」の検索対象のセル範囲を指定します。
条件1 すべて 条件範囲1内のどのセルを計算対象にするかの条件(値やセル番地など)を指定します。
条件範囲2 ※省略可 参照 「条件2」の検索対象のセル範囲を指定します。
条件2 ※省略可 すべて 条件範囲2内のどのセルを計算対象にするかの条件(値やセル番地など)を指定します。

引数[条件範囲n,条件n]は条件の数に応じてセットで追加できます。(最大127まで)
引数「合計対象範囲」と引数「条件範囲n」で指定するセル数は一致している必要があります。(不一致の場合、「#VALUE!」のエラーが表示)

SUMIFSの使用結果イメージ

SUMIFSで条件別の合計を集計したイメージは以下の通りです。
今回は商品別で「金額」列の合計を集計しました。

ポイントは、引数の「合計対象範囲」と「条件範囲1」はそれぞれ同じ範囲で固定すること。
詳細は記事下部の「【参考】SUMIFSの集計漏れを防ぐTIPS」を参照。

そして、引数「条件1」は1行ごとにスライドさせることです。

こうすることで、ベースとなる数式を以降のセルへ使い回せるわけですね。

参考記事

参照形式(絶対参照/相対参照)の詳細は以下の記事をご参照ください。

SUMIFSの数式の挿入手順

上記の結果を得るための手順は以下の通りです。

  1. 関数を挿入するセルを選択
  2. =sum」と入力
  3. サジェストから「SUMIFS」を選択し、「Tab」キーで確定
  4. 元データのシートへ移動
    ※今回は「注文テーブル」シート
  5. 合計したいセル範囲を選択
    ※今回は「金額」列
  6. コンマ(,)を入力
  7. 条件の検索対象のセル範囲を選択
    ※今回は「商品名」列
  8. コンマ(,)を入力
  9. 集計表のシートへ移動
    ※今回は「集計表」シート
  10. 条件となるセルを選択
    ※今回はA4セル
  11. Enter」キーで確定
  12. 1行目の数式をコピーし、以降のセルへペースト
    ※今回はB5~B12セルへペースト

手順②の際にIMEを半角英数モードにすること。
手順⑩も必要に応じて参照形式を変更すること。(今回は指定しなくともOK
2種類以上の条件を設定したい場合、手順⑩の後に手順⑥~⑩を繰り返すこと。

【注意】SUMIFSSUMIFの使い分け問題

SUMIFSと兄弟的な関数としてSUMIFがあります。

1~127種類の条件に対応できるSUMIFSと違い、SUMIF1種類の条件しか指定できません。

また、ややこしいのが、合計の対象範囲を指定する引数の位置が異なる点です。

こうしたことから、原則は上位機能となるSUMIFSをメインで使うと良いでしょう。

SUMIFSなら、条件が1種類から使用できますし、後から条件を増やしたい場合の工数も最小限にできます。

逆に、SUMIFでないといけないケースは、関数をセットする対象のExcelブックの拡張子が「.xls」の場合です。

SUMIFSExcel2007から登場した関数であり、同じくExcel2007から登場したExcelブックの拡張子の「.xlsx」と「.xlsm」でないと使えません。

よって、どうしても「.xls」のExcelブックで集計しないといけない場合は、昔から存在するSUMIFの方を使うこととなります。(条件が2種類以上の場合はSUMPRODUCT
新機能が使え、ファイル容量も小さくなる「.xlsx」や「.xlsm」の方が断然メリットがあります。
現在「.xls」のExcelブックを使っている場合は、拡張子を「.xlsx」や「.xlsm」へ移行する調整を行うことがおすすめです。

【参考】SUMIFSの集計漏れを防ぐTIPS

SUMIFSのリスクとしてよくあるのは、関数のセット後、元データへ追加されたレコードが集計範囲から漏れてしまうというケースです。

この原因は、手順⑤⑦の際、セット時点での元データのレコード数で範囲指定しているためです。

よって、SUMIFSのセット時点で、元データにレコードが追加されても問題ない範囲指定を行いましょう。(詳細は以下参照)

通常のセル範囲が対象

元データが通常のセル範囲の場合は、列単位で指定しましょう。

ワークシート上の列番号(アルファベット部分)をクリックすればOKです。

列全体を対象範囲にしているため、当然レコードが増えても計算対象に含まれます。

なお、SUMIFSの数式を横方向にコピペする際、引数「条件範囲n」を横にスライドさせたくない(固定にしたい)場合は、列を絶対参照にしましょう。

テーブルの列が対象

元データがテーブルの場合は、普通にその列を指定すればOKです。

対象の列名が数式上にセットされ、テーブルにレコード追加されても、集計対象に含まれます。(集計対象範囲が自動拡張される)

特に指定がなければ、元データは事前にテーブルにしておくことがおすすめです。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ワークシート関数_SUMIFS.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ブックを開いたら、次の手順を実施してください。(今までの解説のまとめです)

  1. 関数を挿入するセルを選択
  2. =sum」と入力
  3. サジェストから「SUMIFS」を選択し、「Tab」キーで確定
  4. 元データのシートへ移動
    ※今回は「注文テーブル」シート
  5. 合計したいセル範囲を選択
    ※今回は「金額」列
  6. コンマ(,)を入力
  7. 条件の検索対象のセル範囲を選択
    ※今回は「商品名」列
  8. コンマ(,)を入力
  9. 集計表のシートへ移動
    ※今回は「集計表」シート
  10. 条件となるセルを選択
    ※今回はA4セル
  11. Enter」キーで確定
  12. 1行目の数式をコピーし、以降のセルへペースト
    ※今回はB5~B12セルへペースト

本記事の解説と同じ結果になればOKです!

さいごに

いかがでしたでしょうか?

SUMIFSは条件別の合計を自動で集計でき、実務での利用頻度も高いです。

引数の種類も多く、最初は若干難しく感じるかもしれませんが、集計を自動化/仕組み化する上でマストな関数のため、必ず覚えることをおすすめします。

なお、SUMIFS以外にもExcelでのデータ集計の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。

ご参考になれば幸いですm(_ _)m

森田森田

私が新卒の頃はExcel2003だったため、SUMIFSはなく、条件の数に応じて関数を使い分ける必要がありました。
1種類ならSUMIF2種類以上ならSUMPRODUCT

それが、今ではSUMIFSだけ覚えれば良いわけなので、めちゃくちゃシンプルですね。

これから関数で条件別の合計を集計する方法を覚える方は、脳のキャパの無駄遣いをしないためにも、SUMIFSだけ覚えてしまいましょう!
(「.xls」のExcelブックを扱わない限りは)