エクセルSUMPRODUCT関数で複数条件で抽出した数値を合計する

エクセルSUMPRODUCT関数で、複数条件で抽出した数値を合計する方法を紹介しています。エクセルSUMPRODUCT関数は積の合計を求める関数ですが、複数条件を指定して、複雑な抽出で合計を求めることができます。その他に、条件に一致するセルの個数を数えたりすることもできます。SUMPRODUCT関数は使いにくい、と思われがちですが、そのようなことはありません。SUMPRODUCT関数の色々な計算方法を、分かりやすく説明していきます。

 

 

SUMPRODUCT関数

引数として指定した配列の積を計算し、次にその和を返す関数です。複数条件で抽出した数値を合計することもできます。

SUMPRODUCT関数の書式と引数

SUMPRODUCT(配列 1, [配列 2], [配列 3], …)

配列 1 計算の対象となる要素を含む、最初の配列引数を指定します。
[配列 2], [配列 3], …. 省略できます。計算の対象となる要素を含む配列引数を指定します。
積の合計、範囲の合計を求める(SUMPRODUCTの基本)

SUMPRODUCT関数は、積の合計を求める関数です。SUMPRODUCT関数の基本的な使い方を、おさらいしてみましょう。

積を計算して合計を求める

F2に、「在庫×単価」を求める式を入力しましょう。

F2 =SUMPRODUCT(C2:C9,D2:D9)

「9,570」が返されます。

この式を説明すると、次の式になります。

F2 =C2*D2+C3*D3+C4*D4+C5*D5+C6*D6+C7*D7+C8*D8+C9*D9

 

 

配列の対応する要素間の積を合計

H2に、「(品物の個数)×単価」を求める式を入力しましょう。

H2 =SUMPRODUCT(C2:D9,E2:F9)

「14,940」が返されます。

この式を説明すると、次の式になります。

H2 =(C2*E2+C3*E3+C4*E4+C5*E5+C6*E6+C7*E7+C8*E8+C9*E9)+(D2*F2+D3*F3+D4*F4+D5*F5+D6*F6+D7*F7+D8*F8+D9*F9)

範囲の合計を求める

F2に、在庫数の合計を求める式を入力しましょう。

F2 =SUMPRODUCT(C2:C9)

「86」が返されます。

この式を説明すると、次の式になります。

F2 =C2+C3+C4+C5+C6+C7+C8+C9

 

 

条件に一致するセルの合計を求める

条件に一致したセルを合計

G2に、品番「A102」に一致する在庫の合計を求める式を入力しましょう。

G2 =SUMPRODUCT((A2:A9=”A102″)*1,C2:C9)

「21」が返されます。

セルを参照する式は、次のようになります。

G2 =SUMPRODUCT((A2:A9=E2)*1,C2:C9)

条件に一致した積の合計を求める

G2に、店舗「青山」に一致する「在庫×単価」の合計を求める式を入力しましょう。

G2 =SUMPRODUCT((B2:B9=”青山”)*1,C2:C9*D2:D9)

「4,570」が返されます。

セルを参照する式は、次のようになります。

G2 =SUMPRODUCT((B2:B9=F2)*1,C2:C9*D2:D9)

 

 

複数条件に一致するセルの合計を求める

複数条件に一致したセルを数える

H2に、品番「A108」かつ在庫「>=10」に一致する在庫を数える式を入力しましょう。

H2 =SUMPRODUCT((A2:A9=”A108″)*(C2:C9>=10))

一致する在庫は2つです。「2」が返されます。

セルを参照する式は、次の式です。

H3 =SUMPRODUCT((A2:A9=F3)*(C2:C9>=G3))

複数条件に一致したセルを合計

G2に、品番「A102」かつ 店舗「東京」に一致する「在庫」の合計を求める式を入力しましょう。

G2 =SUMPRODUCT((A2:A9=”A102″)*(B2:B9=”東京”),C2:C9)

「15」が返されます。

セルを参照する式は、次のようになります。

G2 =SUMPRODUCT((A2:A9=F2)*(B2:B9=G2),C2:C9)

 

 

複数条件に一致したセルの積を合計

G2に、品番「A108」かつ 店舗「青山」に一致する「在庫×単価」の合計を求める式を入力しましょう。

G2 =SUMPRODUCT((A2:A9=”A108″)*(B2:B9=”青山”),C2:C9*D2:D9)

「2,530」が返されます。

セルを参照する式は、次のようになります。

G2 =SUMPRODUCT((A2:A9=F2)*(B2:B9=G2),C2:C9*D2:D9)

SUMPRODUCT関数は配列数式ができる便利な関数です

エクセルSUMPRODUCT関数は複数条件で抽出した数値を合計できる関数ですが、SUMPRODUCT関数以外にも複数条件で抽出した値を合計できる関数はあります。DSUM関数、SUMIFS関数が複数条件で値を合計できます。SUMPRODUCT関数との違いは、SUMPRODUCT関数は配列計算ができることです。数を数えたり合計を求めることができる便利な関数です。

こちらの記事もご覧ください。⇒エクセルDSUM関数で複数条件を指定して抽出した数値を合計する