=SUMPRODUCT(B2:B6,C2:C6)は、B列の各数量にC列の隣の価格を掛け、その結果を合計します。各行の小計の列を作らずに、注文の合計を1つのセルで出せます。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Line total | Total | |
| 2 | Pen | 4 | $1.50 | $6.00 | $30.70 | |
| 3 | Notebook | 2 | $3.25 | $6.50 | $30.70 | |
| 4 | Folder | 5 | $0.80 | $4.00 | ||
| 5 | Stapler | 1 | $7.90 | $7.90 | ||
| 6 | Marker | 3 | $2.10 | $6.30 |
F2とF3は同じ$30.70を表示します。D列の小計は、SUMPRODUCTが何をしているかを見せるためだけにあります。4 × 1.50、2 × 3.25と順に掛け、最後にSUMします。数量を変えると、両方の合計がそれに合わせて変わります。
SUMPRODUCT関数の構文
=SUMPRODUCT(array1, [array2], [array3], ...)
- 各配列は範囲か、範囲を作り出す計算で、すべて同じ大きさでなければなりません。そうでないとSUMPRODUCTは
#VALUE!を返します。 - 配列が2つ以上なら、同じ位置の値どうしを掛け、その積を足します。
- 配列が1つならそれを合計するだけで、下の条件付きの形はこれを利用しています。
=SUMPRODUCT((A2:A7="North")*C2:C7)の配列は1つで、すでに掛け算が済んでいます。 - 独立した引数として渡した文字列は0として数えます。
*の計算の中の文字列は#VALUE!の原因になります。
SUMPRODUCTはどのバージョンのExcelでも、Ctrl+Shift+Enter(MacではCmd+Shift+Enter)なしで配列を扱えます。そのためSUMIFSが登場する前は条件付き合計の定番の道具で、SUMIFSでは扱えない場合には今もそうです。
SUMPRODUCTで条件を使う
範囲に対する比較A2:A7="North"は、行ごとにTRUEかFALSEを1つ返します。それを掛けると、TRUEの行は残り(×1)、ほかの行は0になります(×0)。ANDにするには2つの比較を掛けます。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Formula | Result | |
| 2 | North | Apple | 120 | North sales | 230 | |
| 3 | South | Pear | 45 | North Apple sales | 150 | |
| 4 | North | Pear | 80 | Count North | 3 | |
| 5 | East | Apple | 55 | Count over 50 | 4 | |
| 6 | South | Apple | 200 | Without -- | 0 | |
| 7 | North | Apple | 30 |
F2はNorthの3行を足して230です。F3は2つの条件を掛けているので、両方がTRUEの行だけが数えられて150です。合計ではなく件数を出すには、値を省き、--(2つのマイナス記号)でTRUE/FALSEを数値に変えます。F4はNorthの3行を数えます。F6は--が大切な理由を示しています。SUMPRODUCTはTRUEの値を足さないので、--のない数式は0を返します。
最初の4つは、SUMIF、SUMIFS、COUNTIFと同じ結果になります。SUMPRODUCTの出番は次の節です。
SUMIFSで表せない条件
SUMIFSは列を固定の条件と比べます。日付の月を取り出したり、2つの列どうしを比べたり、足す前に数量と価格を掛けたりはできません。SUMPRODUCTならできます。それぞれの条件がふつうの計算だからです。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Date | Target | Actual | Formula | Result | |
| 2 | North | 2026-01-05 | 100 | 120 | February sales | 135 | |
| 3 | South | 2026-01-12 | 60 | 45 | Rows over target | 3 | |
| 4 | North | 2026-02-03 | 90 | 80 | North or East sales | 285 | |
| 5 | East | 2026-02-18 | 50 | 55 | Above target by | 75 | |
| 6 | South | 2026-03-02 | 150 | 200 | |||
| 7 | North | 2026-03-20 | 40 | 30 |
- G2はすべての日付のMONTHを求め、2月の行を残します：80 + 55 = 135。これはどの年の2月も足します。1つの年だけにするには
*(YEAR(B2:B7)=2026)を加えます。 - G3は2つの列を行ごとに比べ、ActualがTargetを上回った行を数えます。
- G4はORです。2つの条件を足すと、どちらかがTRUEなら1になります(両方なら2になるので、
>0を付けています)。NorthまたはEastで285です。 - G5は、目標を上回った行についてだけ、各行が目標をどれだけ上回ったかを足します。
SUMPRODUCTで重み付きの合計と平均を出す
数量と価格の積は重み付きの合計で、条件を加えることもできます。同じ考え方で重みの合計で割れば加重平均になります。=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)は、売れた商品1個あたりの平均価格です。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Qty | Price | Formula | Result | |
| 2 | North | Apple | 10 | $1.20 | North revenue | $31.00 | |
| 3 | South | Pear | 4 | $1.50 | All revenue | $61.00 | |
| 4 | North | Pear | 6 | $1.50 | Average price per item | $1.36 | |
| 5 | East | Apple | 8 | $1.20 | |||
| 6 | South | Apple | 12 | $1.20 | |||
| 7 | North | Plum | 5 | $2.00 |
Northは$1.20のAppleを10個、$1.50のPearを6個、$2.00のPlumを5個売ったので、G2は$31.00を表示します。価格の単純な平均では、PlumがAppleと同じ数だけ売れたかのように扱われます。G4は各価格を数量で重み付けしています。
練習: 条件付きの売上金額
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Qty | Price | Formula | Result | |
| 2 | North | Apple | 10 | $1.20 | South revenue | ||
| 3 | South | Pear | 4 | $1.50 | |||
| 4 | North | Pear | 6 | $1.50 | |||
| 5 | East | Apple | 8 | $1.20 | |||
| 6 | South | Apple | 12 | $1.20 | |||
| 7 | North | Plum | 5 | $2.00 |
やってみよう: Southの売上金額を計算してください。Southの行についてだけ、数量と価格を掛けます。数式はG2に書きます。
SUMPRODUCTとSUMIFSの違いと、2つのエラー
| 条件 | SUMIFS | SUMPRODUCT |
|---|---|---|
| 列がある値と等しい | =SUMIFS(C2:C7,A2:A7,"North") | =SUMPRODUCT((A2:A7="North")*C2:C7) |
| 文字列を含む | =SUMIFS(C2:C7,B2:B7,"*app*") | =SUMPRODUCT(ISNUMBER(SEARCH("app",B2:B7))*C2:C7) |
| 日付の月 | 直接はできない | =SUMPRODUCT((MONTH(B2:B7)=2)*D2:D7) |
| 列どうしの比較 | できない | =SUMPRODUCT(--(D2:D7>C2:C7)) |
| 数量 × 価格 | できない | =SUMPRODUCT(C2:C7,D2:D7) |
SUMIFSで済むときは、いつもSUMIFSを選んでください。読みやすく、数万行でも速く、列全体を指定できます。=SUMPRODUCT((A:A="North")*C:C)は100万行以上を掛け算し、C1の見出しの文字列に達したとたんに#VALUE!を返すので、SUMPRODUCTにはA2:A500のような正確な範囲を渡してください。
出会うエラーは2つです：
- 範囲の大きさが違うことによる
#VALUE!。=SUMPRODUCT(B2:B6,C2:C7)は失敗します。すべての範囲が同じ行を対象にする必要があります。 - 掛け算する範囲の文字列による
#VALUE!。C2:C7の中に見出しや「n/a」があると(A2:A7="North")*C2:C7は壊れます。文字列は掛け算できないからです。範囲を見出しの下から始めるか、値を別の引数として渡します。=SUMPRODUCT(--(A2:A7="North"),C2:C7)はC列の文字列を0として扱います。
よくある質問
ExcelのSUMPRODUCTは何をしますか？
範囲を行ごとに掛け合わせ、その積を合計します。=SUMPRODUCT(B2:B6,C2:C6)はB2C2 + B3C3 + ... + B6*C6で、たとえば数量と価格を掛けて注文の合計を出します。
SUMPRODUCTで条件を使うには？
比較を掛けます。=SUMPRODUCT((A2:A7="North")*C2:C7)はNorthの行についてC2:C7を足します。比較はTRUEかFALSEを返し、掛けると1か0になります。
SUMPRODUCTの--はどういう意味ですか？
2つのマイナス記号で、TRUEとFALSEを1と0に変えます。=SUMPRODUCT(--(C2:C7>50))は50より大きい値を数えます。これがないとSUMPRODUCTはTRUE/FALSEを0として扱い、0を返します。
SUMPRODUCTとSUMIFSのどちらを使うべきですか？
SUMIFSの条件で表せるならSUMIFSを使います。読みやすく、大きな範囲でも速いからです。日付の月、ある列と別の列の比較、数量と価格の積のように、条件に計算が必要なときはSUMPRODUCTを使います。
SUMPRODUCTが#VALUE!を返すのはなぜですか？
範囲の大きさが違う(B2:B6とC2:C7)か、*で掛けた範囲に文字列が入っているからです。すべての範囲を同じ大きさにし、文字列を含む範囲は別の引数として渡してください。そうすれば文字列は0として扱われます。
