SUMPRODUCT関数での複数条件の合計・集計が便利な理由
ここでは、SUMPRODUCT関数で「複数の条件に合ったデータを合計する方法」について解説します。
SUMPRODUCT関数はSUMIFS関数と同じ動きをする関数ですが、SUMIFS関数よりも優れた点があります。
SUMPRODUCT関数の使い方や、メリットなどを確認していきましょう。
１．SUMPRODUCT関数を使った例を確認
まず、「SUMPRODUCT関数を使った複数条件のデータの合計」がどのような形になるかを見てみましょう。
「条件が一つの場合」と「条件が複数の場合」の２パターンを、SUMIFS関数と比べながら見てみます。
１－１．条件が一つの場合
まずは、「条件が一つの場合」からです。
上の表は、お金の出入りを入力したものです。日付と入出金の区分、勘定科目（内容）、金額が入っています。
上の表をもとに「内容が『売上』」の金額を合計してみます。
結果、2・4・5・7・9・10行目が条件に合ったため、SUMPRODUCT関数は「187,000」を返してきました。
この時数式は、
=SUMPRODUCT((D2:D13="売上")*1,E2:E13)
となります。
これに対してSUMIFS関数で同じことをやろうとすると数式は、
=SUMIFS(E2:E13,D2:D13,"売上")
となり、どちらかというとSUMPRODUCT関数のほうが、直感的に条件式が分かりやすい印象です。
１－２．複数条件の場合
次に、「複数条件の場合」を見てみます。
先ほどと同じ表から、「営業所は『A』」と「内容は『売上』」という2つの条件に合うデータの金額を合計してみます。
結果、2・5行目のデータが条件に合ったため、SUMPRODUCT関数は「32,000」を返してきました。
この時数式は、
=SUMPRODUCT((C2:C13="A")*(D2:D13="売上"),(E2:E13))
となります。
対してSUMIFS関数だと、
=SUMIFS(E2:E13,C2:C13,"A",D2:D13,"売上")
となり、こちらもSUMPRODUCT関数の方が、どのような条件にしたか見やすい気がします。
このように、SUMPRODUCT関数だと「条件式が見やすく」なります。
また、詳しくは後述しますが、「SUMIFS関数だと引数で他ブック参照するとエラーになりますが、SUMPRODUCT関数ではエラーにならない」ことも使った方がいい理由の一つです。
なお、SUMPRODUCT関数の基本的な使い方は次のページで詳しく解説しています。
２．SUMPRODUCT関数を使った数式の作り方
では次に、SUMPRODUCT関数を使った数式の作り方を説明します。
「条件が一つの場合」と「複数条件の場合」でルールが少し違いますので２パターン見てみましょう。
２－１．条件が一つの場合
まずは「条件が一つ」の場合です。
ここでは、先ほどの例と同じように「内容が『売上』」の金額の合計を求めてみます。
（１）数式バーに「=SUMPRODUCT(」と入力後、fxボタンを押す
（２）「配列１」に条件式を入れてカッコでくくり「*1」を付け、「配列２」には合計したい範囲を指定する
（３）OKを押せば完了
条件が一つの場合のポイントは、以下の３つです。
① 条件式を「配列1」に入れ、カッコでくくる
② 条件式の後に「*1」を入れる
③ 合計範囲は「配列2」に入れる
特に②がなぜ必要か疑問だと思いますが、詳しく説明すると長くなるので、SUMPRODUCT関数で条件が一つの場合にはこういうものだと覚えてください。
２－２．複数条件の場合
次に「複数条件の場合」を見てみましょう。
先ほどと同じ表から、「営業所は『A』」と「内容は『売上』」という2つの条件に合うデータの金額を合計してみます。
（１）数式バーに「=SUMPRODUCT(」と入力後、fxボタンを押す
（２）「配列１」に条件式を入れてそれぞれカッコでくくって「*」でつなげ、「配列２」には合計したい範囲を指定する
（３）OKを押せば完了
こちらも、ポイントは以下の３つです。
① 条件式を「配列1」に入れ、カッコでくくる
② 条件式を「*」でつなげる
③ 合計範囲は「配列2」に入れる
「*」でつなぐことで「TRUE×TRUE＝1」、「TRUE×FALSE＝0」といったような計算が行毎にされ、結果すべての条件に合う行のみが「1」となって、これをもとに何行を合計するかが決まります。
条件は「*」でつなげば、3つや4つといった複数条件の指定も可能です。
例えば4つの条件だと数式は、
=SUMPRODUCT((条件式1)*(条件式2)*(条件式3)*(条件式4),E2:E13)
といった形になります。
２－３．いろいろな条件式の作り方
では次に、SUMPRODUCT関数での条件式の作り方をもっと掘り下げてみます。
条件式を指定する時の基本的なルールや、比較演算子の使い方です。
２－３－１．条件式指定時の基本的なルール
SUMPRODUCT関数の条件式は、次の６つを守って作り必要があります。
① 条件式は「範囲」→「比較演算子」→「条件値」の順番に書く
※ 例）「A2:A10="合格"」
※ エラーにはなりませんが、一般的にはこの形です
② 指定する範囲は複数列を指定しない
※複数列指定すると#VALUE!エラーになります
③ 条件値は直接指定とセル参照のどちらでも可
※「A2:A10="合格"」と「セルB1に『合格』と入力して『A2:A10=B1』」はどちらも同じ意味になります
④ 条件式ごとにカッコでくくる
※ 例）「(A2:A10="合格")」、「(B2:B10="国語")」
⑤ 条件一つは後ろに「*1」を付け、複数は「*」でつなげる
※条件が一つの時：「(A2:A10="合格")*1」
条件が二つの時：「(A2:A10="合格")*(B2:B10="国語")」
⑥ 条件値が文字列、日付の場合には""でくくる
※ 例）「(A2:A10="合格")」、「(B2:B10="2021/4/1")」
以上の６つを守れば、正しい条件式が書けます。
２－３－２．比較演算子の使い方
比較演算子とは、「=」や「<」、「>」のことです。
SUMPRODUCT関数に限らずエクセルでは、これらの比較演算子を使って、「等しい」、「等しくない（以外）」、「以上」、「以下」、「超える」、「未満」といった条件を指定することができます。
比較演算子のそれぞれの意味や書き方は、次の通りです。
| 演算子 | 意味 | 入力例 |
| ＝ | 等しい | ＝２（２と等しい） |
| ＞ | 超える | >２（２を超える） |
| ＜ | 未満 | <２（２未満） |
| ＞＝ | 以上 | >=２（２以上） |
| ＜＝ | 以下 | <=２（２以下） |
| ＜＞ | 以外 | <>２（２以外） |
例えば「＝」は「等しい（同じ）」で、「(A2:A10=1)」だと条件は「値が『1』のセル」となります。
「＜」は「未満」で(A2:A10<1)」だと「1未満」、「＜＝」は「以下」で「(A2:A10<=1)」だと「1以下」という条件になります。
「＞」は「を超える」で「(A2:A10>1)」だと「1を超える」、「＞＝」は「以上」で「(A2:A10>=1)」だと「1以上」です。
そして「＜＞」は「以外」で「(A2:A10<>1)」だと「1以外」となります。
このように、指定したい条件に応じて比較演算子を使い分けましょう。
【口コミ・評判のいいExcel本の紹介】
３．SUMPRODUCT関数のメリット・デメリット
最後に「SUMPRODUCT関数を使うメリットとデメリット」を説明します。
３－１．SUMPRODUCT関数のメリット
SUMPRODUCT関数を使う一番のメリットは「SUMIFS関数と違って計算式の中で他ブックを参照してもエラーが出ない」ことです。
SUMIFS関数は「他ブック参照でエラーが出る」のが難点で、引数で他のブックのセルや範囲を指定し、そのブックを閉じると#VALUE!エラーになります。
ですから、他のファイルのデータを条件指定して合計したい時にはSUMPRODUCT関数を使ったほうがいいのです。
また、一番始めで見たように、SUMPRODUCT関数を使った数式は「条件式が分かりやすい」こともメリットの一つです。
３－２．SUMPRODUCT関数のデメリット
反対に、SUMPRODUCT関数のデメリットは「SUMPRODUCT関数を知らない人が多い」ことです。
SUMIFS関数に比べてメジャーでないこの関数は、何を求めようとしているのかを分からない人が多いのです。
また、SUMPRODUCT関数はいろいろなことができる関数なので、その面でも作った人以外は分かりづらいかもしれません。
３－３．SUMPRODUCT関数とSUMIFS関数の使い分けのポイント
以上のことから「他のファイルのデータを条件指定して合計する時にはSUMPRODUCT関数」を、「同じファイルのデータを条件指定して合計する時にはSUMIFS関数」を利用することをお勧めします。
以上、参考になれば幸いです。
SUMPRODUCT関数の、他の記事へのリンクはこちらです。
- SUMPRODUCT関数その他の記事へのリンクはこちら
- ◎基本的な使い方
- ◎複数条件満たすセルをカウント
- ◎複数条件の合計・集計が便利な理由
- ◎行ごとに掛け算した値の合計を求める