エクセルのSUMPRODUCT関数で複数条件を設定してカウントする方法と使い方【コピペ用テンプレートあり】
はじめに
エクセルの SUMPRODUCT関数を使うと、条件を複数設定して、それに合致したデータ数をカウントできます。
下にテンプレート（セルデータサンプル）があるので、
SUMPRODUCT関数とは
SUMPRODUCT関数には、以下の機能・特徴があります。
- CSE（Ctrl + Shift + Enter）なしで 配列数式 が使用可能
- 配列を渡し、その配列の要素同士を乗算し、それらの合計値を返す（本来の関数の機能）
CSEとは
CSEとは、計算式の中で 配列数式 を使用するときに必要なキー操作のことです。
Enter のみではなく、
Ctrl + Shift + Enter
を押す必要があります。※ エクセル2021/365以降では、CSEなしで 配列数式 が使用できます（動的配列数式）。
特に上記の1つ目は、他のほとんどの関数にはない機能で、 何も考えず 配列数式 が使えるようになる例外的で特殊な関数 であると言えます。
SUMPRODUCT関数の基本的な使い方
SUMPRODUCT関数の基本的な使い方は、以下の通りです。
配列1, 配列2, ... )配列1, 配列2, 配列3, ...の
要素同士をそれぞれ乗算し、すべてを合計する。
配列は最大255まで設定できる。
すべての配列のサイズは一致している必要がある。
配列1配列2 , ... エクセルバージョン2019 / 365以前でも、
引数の区切り記号で、演算を変えられる
SUMPRODUCT関数は、 配列数式 を使用しても、通常の計算式のようにCSEなしで動作するため、
あたかも 「引数を区切る記号を変えることで、乗算以外の演算に切り替えられる」 かのように振舞います。
- （通常の使い方）
- 要素同士を 乗算 して合計： SUMPRODUCT(B3:B7,C3:C7)
- （配列数式使用）
- 要素同士を 乗算 して合計： SUMPRODUCT(B3:B7*C3:C7)
- 要素同士を 除算 して合計： SUMPRODUCT(B3:B7/C3:C7)
- 要素同士を 加算 して合計： SUMPRODUCT(B3:B7+C3:C7)
- 要素同士を 減算 して合計： SUMPRODUCT(B3:B7-C3:C7)
実際には、「引数の区切り記号を変更」しているのではなく「第1引数中の 配列数式 の演算内容を変更」しています。
条件付きカウントの実現方法
SUMPRODUCT関数が持つ2つの機能・特徴を組み合わることで、
- 計算1. 配列数式 で条件式を設定し、条件に合う要素のみ 1 （他は0）の配列を生成
- 計算2. 1の配列を SUMPRODUCT関数に渡し、合計値を取得
SUMPRODUCT関数は、引数として複数の配列を受け取れますが、
エクセルでの算出方法
以下の 全セルをコピー ボタンをクリックし、エクセルの A1 セルに貼り付けると、
データを書き換える場合は、
計算の仕組み
本サンプル中では、以下の4パターンの条件設定でデータ数をカウントしています。
- 単一条件
- AND条件 : 単一条件を積 * で結合
- OR条件（重複なし）: 単一条件を和 + で結合
- OR条件（重複あり）: 単一条件を和 + で結合し、条件 >0 を追加
条件設定は 配列数式 内で行っているため、
データ数をカウントするロジック自体は、次の2つで構成されています。
- 計算1. 配列数式 で条件式を設定し、条件に合う要素のみ 1 （他は0）の配列を作成
- 計算2. 1の配列を SUMPRODUCT関数に渡し、合計値を取得
以下では、各4つのパターンそれぞれの算出手順（主に計算1）について解説します。
単一条件のとき
- 計算式： =SUMPRODUCT(--(A3:A8="a"))
- 設定されている条件： A3:A8配列の中で a のもの
上記の条件を設定した 配列数式 を、SUMPRODUCT関数に渡しています。
論理値から数値への変換（-- または 1* の意味）
SUMPRODUCT関数は、受け取った配列要素が 論理値 の場合、正しく認識しません。
そのため受け渡す直前に、 論理値 を 数値 に変換する必要があります。比較演算（条件式）の結果は論理値となり、数式演算（四則演算）の結果は数値となります。
これを利用し、論理値を数値に変換する代表的な方法として以下の2つがあります：
- 前に--を付ける : の乗算を行う
- 前に1* / 後に*1を付ける : の乗算を行う
論理値と数値の対応関係
論理値（TRUE / FALSE）は、以下の数値に対応します。
- TRUE = 1
- FALSE = 0
AND条件のとき
- 計算式： =SUMPRODUCT((A3:A8="a")*(B3:B8=3))
- 設定されている条件： A3:A8配列の中で a、かつ、B3:B8配列の中で 3 のもの
配列数式 上で、2つの条件を「かつ」で結ぶには、 乗算 を使います。
OR条件（重複なし）のとき
- 計算式： =SUMPRODUCT((A3:A8="a")+(A3:A8="b"))
- 設定されている条件： A3:A8配列の中で a、または、A3:A8配列の中で b のもの
配列数式 上で、重複のない2つの条件を「または」で結ぶには、 和算 を使います。
OR条件（重複あり）のとき
- 計算式： =SUMPRODUCT(--((A3:A8="a")+(B3:B8=3)>0))
- 設定されている条件： A3:A8配列の中で a、または、B3:B8配列の中で 3 のもの
配列数式 上で、重複のある2つの条件を「または」で結ぶには、 和算 + >0 の論理演算 を使います。