SUMPRODUCT関数の使い方|複数条件のカウントや合計

ここでは、Excelの「SUMPRODUCT関数の使い方」を解説します。

 

このページではまず、SUMPRODUCT関数とはどのような関数か、基本的な動きや使い方を説明します。

1.SUMPRODUCT関数とは

まずは、SUMPRODUCT関数とはどのような関数かから見てみます。

 

1-1.SUMPRODUCT関数はマルチな関数

SUMPRODUCT関数とは、「積(掛け算)の合計ができる」関数です。ただし、それだけではありません。そこから発展して、次のことができます。

SUMPRODUCT関数でできること
  • 積(掛け算)の合計
  • 条件に合うセルのカウント(=COUNTIF)
  • 条件に合うセルの合計(=SUMIF)
  • 条件に合う積の合計

簡単にいうと、SUMPRODUCT関数は「引数の書き方」でできること(返ってくる値)が変わるのです。

 

なぜこのようなマルチなことができるかはこの後解説しますが、COUNTIF関数やSUMIF関数と比べて良い点もあり、使いこなせるととても便利な関数です。

 

1-2.「積の合計」を例で確認

では、SUMPRODUCT関数で「積の合計」を求めた例を見てみましょう。

上の表から、「全ての行の単価と個数の積(掛け算)の合計」を求めてみます。

 

結果、「3行目から7行目までの全ての単価と個数を掛け合わせた後の合計」をSUMPRODUCT関数で求めることができました。

 

これが「積の合計」です。

2.SUMPRODUCT関数でできることを確認

次に、「積の合計」の他にSUMPRODUCT関数でできることを目的別に見ていきます。

 

2-1.(複数)条件に合うセルをカウントする

SUMPRODUCT関数は、「条件に合うセルの数をカウント」することができます。なお、複数条件にすることも可能です。

できることはCOUNTIF関数と同じですが、SUMPRODUCT関数は「別ファイルの値を参照してもエラーにならない」ため、活躍する場面があります。

 

2-2.(複数)条件に合うセルを合計する

SUMPRODUCT関数は、「条件に合うセルを合計」することもできます。なお、複数条件にすることも可能です。

できることはSUMIF関数と同じですが、SUMPRODUCT関数はやはり「別ファイルの値を参照してもエラーにならない」ため、こちらも活躍する場面があります。

 

2-3.(複数)条件に合う行の積の合計をする

SUMPRODUCT関数は、「条件に合う行の積を合計」することもできます。なお、複数条件にすることも可能です。

これは「積の合計」と「条件指定」を組み合わせたもので、それぞれの積の結果や条件一致/不一致を表示させるセルの必要なく、求めることができます。

3.積の合計を求めるには

まずは、SUMPRODUCT関数を使って「積の合計」を求める時の「数式の形」と「ポイント」、「数式の例」を見てみます。

 

3-1.数式の形は

積の合計を求める場合、SUMPRODUCT関数は次のようになります。

=SUMPRODUCT(範囲1,範囲2…)

掛け合わせたい範囲を「,」で区切って指定します。

 

3-2.数式作成時のポイント

ポイントは、次の通りです。

ポイント
  • 1範囲につき1列指定する
  • 各範囲の行番号を合わせる

1範囲に複数列指定すると、#VALUE!エラーになります。

 

また、「行番号を合わせる」とは、「縦の範囲を合わせる」ということです。例えば1つ目の行範囲が「2~8行目」であれば、他の範囲も「2~8行目」にします。

 

3-3.数式の例

数式の例を一つ見てみます。

先程見た例の、セルD8に入れた数式は次の通りです。

=SUMPRODUCT(C3:C7,D3:D7)

 

これで、「セルC3~C7」と「セルD3~C7」を掛け算し合計した値ががえってきます。

4.(複数)条件に合うセルをカウントするには

次に、SUMPRODUCT関数を使って「条件に合うセルの数」を求める時の「数式の形」と「ポイント」、「数式の例」を見てみます。

 

4-1.数式の形は

条件に合うセルをカウントする場合、SUMPRODUCT関数は次のようになります。

【条件が一つの場合】

=SUMPRODUCT((条件式)*1)

 

【複数条件の場合】

=SUMPRODUCT((条件式1)*(条件式2)…)

条件が一つの場合は、カッコで囲んだ条件式に1を掛けます。複数条件の時には、カッコで囲んだ条件式同士を掛け合わせます。

 

4-2.数式作成時のポイント

次に、ポイントを分けて確認します。

 

4-2-1.数式の作り方

まず、数式は次のような形で書きます。

ポイント
  • 条件式をカッコで囲む
  • 条件が一つの場合はカッコの後に「*1」を付ける
  • 複数条件の場合はカッコ同士を「*」で繋げる

特に複数条件の時には、条件式同士を掛け合わせるイメージで「*」を入れます。

 

4-2-2.「条件式」の作り方

また、「条件式」は次のように書きます。

ポイント
  • 条件式の形は「範囲」→「比較演算子」→「条件値」
  • 「範囲」は1列で指定する
  • 各範囲の行番号を合わせる
  • 「比較演算子」は「=」、「>」、「<」、「>=」、「<=」、「<>」のどれか
  • 「条件値」は「セル番号や数値→そのまま」、「文字列や日付→ 『"』で囲む」

条件式は、「範囲 → 比較演算子 → 条件値」の順で書きます。例えば、「セルA1からA10で『10』以上」という条件にしたい場合には、

A1:A10>=10

となり、範囲が「A1:A10」、比較演算子が「>=」、条件値が「10」となります。

 

なお、条件値は「セル番号か数値」の場合にはそのまま入れますが、「文字列か日付、時刻」の場合には「"」で囲みます。

 

4-3.数式の例

数式の例を一つ見てみます。

先程見た例の、セルC11に入れた数式は次の通りです。

=SUMPRODUCT((B3:B7=B11)*1)

 

これで、「セルB3~B7」の範囲での「エクセル関数研修」のセルの数が返ってきます。

 

4-4.もっと詳しい解説も

次のページでは、SUMPRODUCT関数で(複数)条件に合うセルをカウントする方法をもっと詳しく解説しています。

 

よかったら参考にしてください。

⇒ SUMPRODUCT関数で(複数)条件に合うセルをカウントする方法


口コミ・評判のいいExcel本の紹介】


5.(複数)条件に合うセルを合計するには

続いて、SUMPRODUCT関数を使って「条件に合うセルの合計」を求める時の「数式の形」と「ポイント」、「数式の例」を見てみます。

 

5-1.数式の形は

条件に合うセルを合計する場合、SUMPRODUCT関数は次のようになります。

【条件が一つの場合】

=SUMPRODUCT((条件式)*1,合計範囲)

 

【複数条件の場合】

=SUMPRODUCT((条件式1)*(条件式2)…,合計範囲)

条件が一つの場合は、カッコで囲んだ条件式に1を掛けます。複数条件の時には、カッコで囲んだ条件式同士を掛け合わせます。

 

そしてどちらも、最後に「合計範囲」を指定します。

 

5-2.数式作成時のポイント

ポイントは、ほぼ前項と同じです。違うところだけ確認します。

ポイント
  • 「合計範囲」は「合計したい範囲」を1列で指定する
  • 「合計範囲」の行番号は条件式の「範囲」と合わせる

合計範囲には、「合計したい範囲」を1列で指定します。複数列で指定するとエラーになります。

 

5-3.数式の例

数式の例を一つ見てみます。

先程見た例の、セルC12に入れた数式は次の通りです。

=SUMPRODUCT((B3:B7=B12)*1,C3:C7)

 

これで、「セルB3~B7」の範囲での「ノートパソコン」の「個数の合計」が返ってきます。

 

5-4.もっと詳しい解説も

次のページでは、SUMPRODUCT関数で(複数)条件に合うセルを合計する方法をもっと詳しく解説しています。

 

よかったら参考にしてください。

⇒ SUMPRODUCT関数で(複数)条件に合うセルを合計する方法

6.(複数)条件に合う行の積の合計をするには

最後に、SUMPRODUCT関数を使って「(複数)条件に合う行の積の合計」を求める時の「数式の形」と「ポイント」、「数式の例」を見てみます。

 

6-1.数式の形は

条件に合う行の積を合計する場合、SUMPRODUCT関数は次のようになります。

【条件が一つの場合】

=SUMPRODUCT((条件式)*1,範囲1,範囲2…)

 

【複数条件の場合】

=SUMPRODUCT((条件式1)*(条件式2)…,範囲1,範囲2…)

条件が一つの場合は、カッコで囲んだ条件式に1を掛けます。複数条件の時には、カッコで囲んだ条件式同士を掛け合わせます。

 

そしてどちらも、掛け合わせたい「範囲」を「,」で区切って指定します。

 

6-2.数式作成時のポイント

ポイントは、これまでとほぼ同じです。違うところだけ確認します。

ポイント
  • 範囲を複数指定する

複数の範囲を指定することで、それら同士を掛け合わせた後合計してくれます。

 

6-3.数式の例

数式の例を一つ見てみます。

先程見た例の、セルC12に入れた数式は次の通りです。

=SUMPRODUCT((B3:B7=B11)*1,(C3:C7),(D3:D7))

 

これで、「セルB3~B7」の範囲で「ノートパソコン」だけの「個数×単価の合計」が返ってきます。

 

以上、参考になれば幸いです。

1
2
3
4

SUMPRODUCT関数の、他の記事へのリンクはこちらです。

  • SUMPRODUCT関数その他の記事へのリンクはこちら
Top