条件に一致した値の合計値を知りたいSUMIF関数SUMIFS関数-GSS/Excel/関数

条件に合致したデータで合算するSUMIFS関数・SUMIF関数

デイリーで入力される売り上げ計上シートなどを、担当者別に分析したい、会社ごとに分析したいことってありますよね。

今回はSUMIFS SUMIFS関数をご紹介します。

正直SUMIFSでSUMIFを包含するので、SUMIFSだけ書き方を覚えればOKです！早速やっていきましょう。

いつものシートです。[M4]セルにお題が書かれています。

チームごとの案件数と売り上げを計算していきます。

構文

=SUMIFS(合算したい範囲,条件範囲1,条件1,条件範囲2,条件範囲2.....)

=SUMIF(条件範囲,条件,合算したい範囲)

正解例（仮）

まず正解例は下記の通り。

[N6]=SUMIFS(I5:I19,C5:C19,M6)

ただこれも最適解ではないので、その辺りも説明していきます。

案件数は[I5:I19]、チームは[C5:C19]に書かれています。まず最終的に合算したい(sumしたい)範囲を指定します。その後に条件範囲と条件を入力します。

=SUMIFS(合算したい範囲,条件範囲1,条件1,条件範囲2,条件2,......)

上の式で言うと、合算したい範囲は[I5:I19]。

条件範囲1は[C5:C19]。

条件1は[M6]。

今回は条件が1つしかないので条件範囲2以降は続かないのでこれで終了です。

合体させた結果が[N6]=SUMIFS(I5:I19,C5:C19,M6)になります。

条件が増えた場合は、同じように、条件範囲と条件を増やしていくだけです。意外と簡単ですよね？

先ほどこれが最適解ではないと言いました。それはなぜかというと、範囲が全て固定されていない状態のままだからです。

改めて表を見ていきます。

AAA、BBB、CCCそれぞれで案件数と売り上げの合計値を出そうとしています。計算して表示するセルが6つありますが、全て先ほどのように関数を入力するのは手間ですよね？

関数の中の「合算したい範囲」と「条件範囲」が一緒で、「条件」だけが違うので、コピペでいけそうって思いませんか？

結論、いけます。

ただ[N6]=SUMIFS(I5:I19,C5:C19,M6)のまま[N7][N8]セルにコピペをするとこうなります。

一瞬合ってそうって思いますよね。でも範囲をよく見てみてください。

[N7]=SUMIFS(I6:I20,C6:C20,M7)となっており、「合算したい範囲」と「条件範囲」がずれてしまっていることがわかります。

ここで使うのが「$」。範囲を固定します。

絶対参照と相対参照

通常セルを使った関数を別のセルにコピペすると、相対参照され、位置がずれます。コピペ元の6行目から7行目にコピペしたことで、相対参照された結果、指定していた範囲が[I5:I19]から[I6:I20]に行がずれてしまいました。

行がずれることで、指定したい範囲がずれてしまうので、行を固定します。そこで書き直した式が下記です。

[N6]=SUMIFS(I$5:I$19,C$5:C$19,M6)

このセルの状態で、[N6]を[N7][N8]にコピペすると

[N7]=SUMIFS(I$5:I$19,C$5:C$19,M7)

行がずれずに正しい式がコピペできました。

これでやっと正解！

ではありません。まだ続きます。

売上を表示する列が[O]列にありますね。これも[N6]のセルをコピペするだけで出せるようにします。

計算をするデータ元の表を見てみると、[I]列が案件数、[J]列が売上になっています。

計算式を入れる表は[N]列が案件数[O]列が売上と同じく隣り合っています。ここは相対参照と絶対参照を組み合わせて[N6]に作った関数をコピペして、[N7][N8][O6][O7][O8]全て埋められるようにします。

本当の正解

書き直した式がこちらです。

[N6]=SUMIFS(I$5:I$19,$C$5:$C$19,$M6)

合算したい範囲はI列からJ列にずれて欲しいので、固定しません。よって[I$5:I$19]になります。

条件範囲はずれて欲しくないので、列も行も固定します。よって[$C$5:$C$19]になります。

条件は列はずれて欲しくありませんが、行はずれて欲しいです。よって[$M6]になります。

合わせた結果が[N6]=SUMIFS(I$5:I$19,$C$5:$C$19,$M6)です。

これを適用するすべてのセルにコピペした結果がこちらです。

[O8]=SUMIFS(J$5:J$19,$C$5:$C$19,$M8)

となっていて、「合算したい範囲」が[J]列にずれて、「条件」が[M8]にずれていることがわかります。

このように正しく絶対参照と相対参照を使いこなすことで、作る関数の数を最小限にすることができます。

ぜひ使いこなせるようにたくさん使ってみてくださいね。

まとめ

• SUMIFS関数・SUMIFS関数の使い所
  • あるデータから条件が合致するデータのみで合算したいとき
• 構文
  • =SUMIFS(合算したい範囲,条件範囲1,条件1,条件範囲2,条件範囲2.....)
  • =SUMIF(条件範囲,条件,合算したい範囲)

• 例文
  • SUMIFS(I$5:I$19,$C$5:$C$19,$M6)
  • SUMIF($C$5:$C$19,$M6,I$5:I$19)