Office Hack

ExcelのSUMPRODUCT関数の使い方|範囲または配列の積を合計する

  • Release
  • Update

掛け算した結果が複数あり、それを合計したい場合、SUMPRODUCT関数が便利です。SUM関数でも求めることができますが、SUMPRODUCT関数なら1つの数式で可能です。

SUMPRODUCT関数の書式から使い方、応用まで幅広くご紹介しています。

SUMPRODUCT関数とは?

意味

SUMPRODUCT関数は、範囲または配列に対応する要素の積を合計した結果を返します。

読み方

サムプロダクト

書式と引数

書式は「=SUMPRODUCT(配列,[配列2],[配列3],…)」のように記述します。

SUMPRODUCT関数の書式

SUMPRODUCT関数には次の引数があります。

引数名 説明
配列1(必須) 計算の対象となる要素を含む最初の配列を指定します。
[配列2], [配列3],…(省略可能) 計算の対象となる要素を含む配列を指定します。最大で255個まで指定できます。

SUMPRODUCT関数の使い方(使用例)

実際にSUMPRODUCT関数を使用する例をご紹介します。引数をどのように使用するか確認してみましょう。

作業時間:3分

  1. 関数名を入力する

    関数名を入力する

    上図より価格と個数を掛けた合計を求めたいとします。F10セルを選択し、『=SUMPRODUCT(』と入力します。

  2. 配列1を指定する

    配列1を指定する

    SUMPRODUCT関数の引数「配列1」を指定します。「=SUMPRODUCT(」に続いて『D3:D9,』と入力します。マウスでセルの範囲を選択することでも指定できます。

  3. 配列2を指定する

    配列2を指定する

    SUMPRODUCT関数の引数「配列2」を指定します。「=SUMPRODUCT(D3:D9,」に続いて『E3:E9)』と入力します。マウスでセルの範囲を選択することでも指定できます。数式を確定するために【Enter】キーを押します。

  4. SUMPRODUCT関数の計算結果

    SUMPRODUCT関数の計算結果

    F10セルにSUMPRODUCT関数の計算結果である「2554」が表示されました。各行の価格と個数を掛けたものをSUM関数で合計することもできますが、SUMPRODUCT関数を使うことで1つの数式で範囲の積を合計することが可能です。

SUMPRODUCT関数の応用

複数条件をすべて満たすデータをカウントする

セルの数を数えたい場合、COUNTIF関数を使うことが多いですが、SUMPRODUCT関数でも条件を満たすデータをカウントすることができます。

表の準備

例では性別と出身が書かれた表を用意しました。この表から性別が「男」、出身が「東京」であるAND(かつ)条件をカウントします。

数式の入力

E3セルを選択し、『=SUMPRODUCT((B3:B12="男")*(C3:C12="東京"))』と入力してEnterを押します。

「B3:B12="男"」と「C3:C12="東京"」は論理式です。条件を満たせば真(TRUE)、満たしてなければ偽(FALSE)を返します。

TRUEは1、FALSEは0なので掛け合わせると「TRUE*TRUE=1」「TRUE*FALSE=0」「FALSE*FALSE=0」のようになります。

それぞれの行において論理式の結果は次の表のようになります。結果を合計することで条件にあった数をカウントすることができます。

B3:B12="男" C3:C12="東京" 結果
B3(男)
TRUE
C3(東京)
TRUE
TRUE*TRUE=1
B4(男)
TRUE
C4(千葉)
FALSE
TRUE*FALSE=0
・・・ ・・・ ・・・
B12(不明)
FALSE
C12(不明)
FALSE
FALSE*FALSE=0
数式の計算結果

E3セルにSUMPRODUCT関数の計算結果である「2」が表示されました。SUMPRODUCT関数を使って複数の条件に合った組み合わせをカウントすることができます。

複数条件をすべて満たすデータの合計を求める

複数の条件を満たす積の合計を求めるには上記でご紹介した「複数条件でカウントする」に配列を引数として指定することで求めることができます。

表の準備

上図の例では、店舗ごとに種類、価格、個数が入力されています。この表からA店のりんごの売上を求めたいとします。

数式の入力

①I2セルを選択し、②数式バーに『=SUMPRODUCT((B3:B10="A店")*(C3:C10="りんご"),D3:D10,E3:E10)』と入力します。

2つの条件式「B3:B10="A店"」と「C3:C10="りんご"」の積の結果と「D3:D10」と「E3:E10」の積の結果を掛け合わせます。

数式の結果

I2セルに計算結果である「294」が表示されました。A店かつりんごを満たす行は3行目と6行目になります。この2行のD列とE列の積の合計が294です。

特定の文字列を含むデータの合計を求める

SUMPRODUCT関数を使って特定の文字列を含むデータの合計を求めるには、他の関数と組み合わせる必要があります。

表の準備

上図の例では、店舗ごとに種類、価格、個数が入力されています。この表から種類に「ん」という文字列が含まれる商品の売上を求めたいとします。

数式の入力

①I2セルを選択し、②数式バーに『=SUMPRODUCT(ISNUMBER(FIND("ん",C3:C10))*D3:D10,E3:E10)』と入力します。ISNUMBER関数とFIND関数を組み合わせます。

ISNUMBER関数は、セルの内容が数値の場合に、TRUEを返します。FIND関数は、文字列が他の文字列内で最初に現れる位置を検索します。

FIND関数で文字列の位置を検索し、返り値が数値の場合にTRUEが返ります。行ごとに探している文字列があれば1、なければ0を掛け算していきます。

関数の結果

I2セルに計算結果である「906」が表示されました。文字列「ん」を含むデータは3、5、6、10行目にあります。条件を満たす積の合計が906になります。

日付を条件にする

特定の日付の条件を満たすデータの合計を求める方法を説明します。

表の準備

上図の例では、店舗ごとに種類、価格、個数が入力されています。この表から「2020/2/5」の商品の売上を求めたいとします。

数式の入力

①I2セルを選択し、②数式バーに『=SUMPRODUCT((B3:B10=DATE(2020,2,5))*1,D3:D10,E3:E10)』と入力します。

DATE関数で対象の日付を指定します。論理式は積である必要があるため1つの条件の場合、「(B3:B10=DATE(2020,2,5))*1」のように「1」を掛けます。

関数の結果

I2セルに計算結果である「1938」が表示されました。「2020/2/5」のデータは3~7行目にあります。条件を満たす積の合計が1938になります。

SUMPRODUCT関数と他の関数の組み合わせ

重複しているデータを1件としてカウントする(COUNTIF関数)

SUMPRODUCT関数とCOUNTIF関数を組み合わせると重複しているデータを1件としてカウントすることができます。データの種類を調べてみるときに使ってみましょう。


複数条件に一致するデータを抽出する(INDEX関数)

SUMPRODUCT関数とINDEX関数を組み合わせて複数条件に一致するデータを抽出するやり方をご紹介しています。

SUMPRODUCT関数のエラーの原因と対処方法

範囲のセル数が異なる

VALUEエラー

SUMPRODUCT関数の引数で範囲を指定しますが、その範囲のセル数が異なると「#VALUEエラー」が表示されます。範囲は揃えるようにしましょう。

SUMPRODUCT関数のよくある質問

SUMPRODUCT関数はワイルドカードを使えますか?

SUMPRODUCT関数ではワイルドカード「*」を使用することができません。特定の文字列を含むデータの合計を求めるにはISNUMBER関数とFIND関数を組み合わせます。

詳しいやり方は上記でご紹介している「特定の文字列を含むデータの合計を求める」をご覧ください。

おすすめの商品をご紹介

よろしければ参考にならなかった点をお聞かせください

CAPTCHA


Page Top