Excel SUMPRODUCT関数の使い方｜複数条件で簡単計算

Excelで複数のデータ範囲を掛け算して合計を求めたい時に便利なのが、SUMPRODUCT関数です。

SUMPRODUCT関数は、指定された複数の配列の対応する要素同士を１行ずつ掛け算（PRODUCT）し、結果の合計（SUM）を求めます。例えば、数量と単価の配列を指定すると、各商品の合計金額を一度に計算できます。また、複数の条件を満たすデータの合計を求める際にも利用できます。
本記事では、SUMPRODUCT関数の基本的な使い方と、実務で活用できる応用例を紹介します。

Contents

1 SUMPRODUCT関数の基本構文
2 基本例
- 2.1 基本例1：売上の合計を求める
- 2.2 基本例2：条件付きの合計を求める
  - 2.2.1 A7で指定の「商品名」と一致する行の売上合計を求める
  - 2.2.2 基本例2でSUMPRODUCTを使用しない場合どうなる?
    - 2.2.2.1 SUMIF関数で行う場合列追加が必要になる
3 応用例
- 3.1 応用例1：複数の条件を使って計算する
  - 3.1.1 A7で指定の「商品名」とA9で指定の「単価」以上行の売上合計を求める
- 3.2 応用例2：複数の条件(ANDとOR混在)を使って計算
  - 3.2.1 A7、A8で指定の「商品名」とA10で指定の「単価」以上行の売上合計を求める
4 よくあるエラーと対処法
5 まとめ
6 関連記事

SUMPRODUCT関数の基本構文

=SUMPRODUCT(配列1, 配列2, …)

配列1：計算対象となる最初の範囲
配列2：計算対象となる2番目の範囲（オプションで追加）
…　：最大255個まで指定可能

SUMPRODUCTは、指定された範囲（配列）の同じ位置にある要素同士を掛け算し、その結果を合計します。

基本例

基本例1：売上の合計を求める

=SUMPRODUCT($F3:$F7,$G3:$G7)

解説：

F3:F7（売上数量）と G3:G7（単価）の同じ行を掛け合わせ、結果が加算されます。
結果：
(10×200) + (20×100) + (5×30) + (8×30)+ (10×200)= 8,550

基本例2：条件付きの合計を求める

A7で指定の「商品名」と一致する行の売上合計を求める

=SUMPRODUCT(($E3:$E7=$A$7)*$F3:$F7,$G3:$G7)

解説：

($E3:$E7=$A$7) は条件式です。これにより、商品が”レタス”の行を1（TRUE）に、それ以外の行を0（FALSE）に変換します。
この結果、 E4とE7が”レタス”で対象になる。
条件式の後の、”*“の意味は、その後の値と掛け算します。(AND条件になる)
結果：
(1×20×10) + (1×10×200) = 4,000
E4とE７”レタス”以外の行が加算されないロジックは?
<例:E3の”キャベツ”で説明>
条件式の結果は0（FALSE）であるため、計算式は(0×10×200)となり結果は0になる。

基本例2でSUMPRODUCTを使用しない場合どうなる?

SUMIF関数で行う場合列追加が必要になる

=SUMIF(範囲, 条件, 合計範囲)

=SUMIF($E3:$E7, $A$7,$H3:$H7)

解説：

SUMIFの場合は、売上列を追加して、売上数量×単価の計算が必要になる。
この例は、H列(売上)が予め存在している場合は、SUMIF関数でも充足するが、存在しない場合は、わざわざ列追加するのではなく、SUMPRODUCT関数を使用して、関数内で売上を計算した方が簡単。

応用例

応用例1：複数の条件を使って計算する

A7で指定の「商品名」とA9で指定の「単価」以上行の売上合計を求める

=SUMPRODUCT(($E3:$E7=$A$7)*($G3:$G7>=$A$9),$F3:$F7,$G3:$G7)

解説：

($E3:$E7=$A$7)*($G3:$G7>=$A$9) は掛け算ですので、両方とも真である場合のみ1を返す。
それを基に、数量と単価を掛け合わせて合計します。
一方のみが満たされた場合、条件式の結果は0。計算は「0×数量×単価」となり結果は0となる。

応用例2：複数の条件(ANDとOR混在)を使って計算

A7、A8で指定の「商品名」とA10で指定の「単価」以上行の売上合計を求める

=SUMPRODUCT((($E3:$E7=$A$7)+($E3:$E7=$A$8))*($G3:$G7>=$A$10),$F3:$F7,$G3:$G7)

解説：

①「(($E3:$E7=$A$7)+($E3:$E7=$A$8)) 」は加算対象の「商品名」を２種類判定。”+“(OR)で繋ぐことで、どちらか一致したら対象(TRUE(1))にする。
②「($G3:$G7>=$A$10)」は加算対象の「単価」を判定し200以上なら対象(TRUE(1))にする。
③　①と②を、”*“(AND)で繋ぐことで、両方が一致したら1×1=(TRUE(1))となり対象になる。
④「$F3:$F7,$G3:$G7」は、(TRUE(1))になっている行のF列×G列を計算し売上合計を求める。

よくあるエラーと対処法

エラー例	原因	対処法
#VALUE!	配列のサイズが一致しない	計算する配列の範囲が同じサイズか確認
#NUM!	数字以外の値が含まれている場合	数字以外のセルを取り除くか、エラー処理関数で回避
#REF!	参照範囲が削除された場合	セルの参照範囲を正しく修正