エクセル複数条件での合計の求め方 SUMIFS関数・SUMPRODUCT関数の使い方

前回はエクセルを使いこなせるようになるポイントを紹介させていただきましたが、今回は私がよく使う関数の一つで条件に合致した合計を求める関数を紹介したいと思います。

データベースによって使い分けて使用しておりますが、SUMIFS関数とSUMPRODUCT関数はデータベースから合計を求める際に非常に便利な関数なので覚えておいて損は無いと思います。

もっき

最近のバージョンでは関数を入力するとアシスト機能が働くので関数名だけ覚えれば大丈夫！

1 SUMIFS関数について
- 1.1 SUMIFS関数の使い方
- 1.2 SUMIFS関数で出来ない事
2 SUMPRODUCT関数について
- 2.1 SUMPRODUCT関数の使い方
3 まとめ

SUMIFS関数について

では早速SUMIFS関数について。SUMIFS関数はSUMIF関数の派生で、複数の条件を満たした値の合計を求める事が出来ます。

注意したいのが、SUMIFS関数が使えるのはエクセル2007以降に限ります。

SUMIF関数を使って対戦相手ごとに得失点の合計を求める

上図は４月２９日までのタイガースの対戦成績（実際の結果とは異なります）とします。対戦相手ごとに得失点を求めるとすると条件は一つなのでSUMIF関数で解決できます。

数式バーの下に表示されているのがアシスト機能ですね。

SUMIF(範囲,検索条件,[合計範囲])

となっていますよね。ここでの範囲とは対戦相手が入力されているＢ列で、検索条件とはＢ列の中からどの条件で合計を求めるのか。ここではＨ５となります。最後の合計範囲とは文字通り求めたい合計部分になるので得点はＤ列となり、失点だとＥ列という事になります。

本題の複数条件なった場合はどうなるのか。例えば対戦相手ごとに得失点を求めるんですが、そこに球場が甲子園だったのかという条件を追加してみましょう。

SUMIFS関数の使い方

SUMIFS関数を使って対戦相手ごとにホームゲームとビジターゲームの得点を求める

SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2)

SUMIF関数と違うのは最初に合計範囲を選択する事。

ここでの合計対象範囲とは得点にあたるのでD列となり、条件範囲1に対戦相手のＢ列、条件1に求めたい対戦相手（上図ではベイスターズ）Ｈ１６となり、条件範囲2にはもう一つの条件を入れるので球場のＣ列で条件2はホームゲームなので“甲子園”となります。

甲子園を囲んでいる“”（ダブルクォーテーション）は数字じゃなくて文字列という意味になります。

ちなみにビジターゲームの場合は甲子園以外の得点を求めることになるので条件2が“<>甲子園”となります。

<>という記号はエクセル上でノットイコールという意味になります。

もっき

今回は条件が二つの場合ですが、もっと条件を増やす事も可能です！

SUMIFS関数で出来ない事

こんな便利なSUMIFS関数ですが、出来ない事もあります。

SUMIFS関数は複数の条件に合致した数値の合計を求める関数なので、上記表の場合に得失点では無く勝敗数の合計を求めたい場合にデータベースに○や●が入力されているので合計する事が出来ないんです。

そこで登場するのが次に紹介するSUMPRODUCT関数になります。

SUMPRODUCT関数について

SUMPRODUCT関数とは範囲または配列の対応する要素の積を合計した結果を返し。引数となる配列は行数と列数が等しい配列である必要があります。今回はこのSUMPRODUCT関数を応用した使い方になります。

何やら難しい事が書いてあり意味がわかりませんよね。早速図を使って見ていきましょう。

SUMPRODUCT関数の使い方

SUMPRODUCT関数を使って対戦チームごとの甲子園での白星の数を求める

SUMPRODUCT(配列1,配列2,配列3…)とありますが、ここでの配列とは「条件」だと思ってください。

分かり易くすると

SUMPRODUCT((条件範囲1=条件1)*(条件範囲2<>条件2)*(条件範囲3>=条件3)*(合計対象範囲))

というように複数の条件を積算し、最後に合計範囲を積算する事によって複数条件での合計を求める事が出来ます。

SUMIFS関数の場合は数値での合計対象範囲が必要ですが、SUMPRODUCT関数では合計対象範囲が無くても最後に1を積算（合計対象範囲を*1に）する事で合計を求める事が出来ます。また、イコール（＝）やノットイコール（<>）、ダイナリ（>）ショウナリ（<）ダイナリイコール（>=）ショウナリイコール（<=）等、様々な条件で対象を絞る事も出来るので非常に便利な関数です。

=SUMPRODUCT(($B$2:$B$28=H21)*($C$2:$C$28<>”甲子園”)*($F$2:$F$28=”○”))*1

上記の式の内容を分かり易く説明すると、($B$2:$B$28=H21)という部分が対戦相手ヤクルトという条件になり、($C$2:$C$28<>”甲子園”)という部分が甲子園以外の球場という条件で、最後の($F$2:$F$28=”○”)という部分が勝敗が白星という条件になります。

*（アスタリスク）はエクセル上で積算を意味になります。

SUMPRODUCT関数を使う上で一つ注意しなければならないのが、複数の条件範囲を全て同じ行範囲で指定する事。

=SUMPRODUCT(($B$2:$B$28=H21)*($C$2:$C$28<>”甲子園”)*($F$2:$F$28=”○”))

見てわかる様に行範囲が２～２８で統一されていますよね。行範囲は広くても構いませんが、行の始まりと終わりは必ず統一するようにしましょう。