エクセルのSUMPRODUCT関数で複数条件を設定してカウントする方法と使い方【コピペ用テンプレートあり】
はじめに
エクセルの SUMPRODUCT関数を使うと、条件を複数設定して、それに合致したデータ数をカウントできます。
下にテンプレート（セルデータサンプル）があるので、
SUMPRODUCT関数とは
SUMPRODUCT関数には、以下の機能・特徴があります。
- CSE（Ctrl + Shift + Enter）なしで 配列数式 が使用可能
- 配列を渡し、その配列の要素同士を乗算し、それらの合計値を返す（本来の関数の機能）
CSEとは
CSEとは、計算式の中で 配列数式 を使用するときに必要なキー操作のことです。
Enter のみではなく、
Ctrl + Shift + Enter
を押す必要があります。※ エクセル2021/365以降では、CSEなしで 配列数式 が使用できます（動的配列数式）。
特に上記の1つ目は、他のほとんどの関数にはない機能で、 何も考えず 配列数式 が使えるようになる例外的で特殊な関数 であると言えます。 SUM関数でも、SUMPRODUCT関数と同様の動作が可能になっています。
SUMPRODUCT関数の基本的な使い方
SUMPRODUCT関数の基本的な使い方は、以下の通りです。
SUMPRODUCT関数
第1引数、第2引数の選択範囲間で、 それぞれの要素同士を個別に乗算した後、それらすべてを合計する。
具体的には以下の計算が行われる：
第1引数範囲の 1 番目の値と第2引数範囲の 1 番目の値を乗算する。第1引数範囲の 2 番目の値と第2引数範囲の 2 番目の値を乗算する。- ... これを選択範囲の最後の要素まで繰り返す。
- 1~3の値を合計する
第3引数, 第4引数, ... を追加することで、上記の1~3で乗算される値を増やすことができる（最大引数255）。
すべての引数の範囲の要素数は一致している必要がある。例：
SUMPRODUCT(B3:B7,C3:C7)
- 第1引数 (
B3:B7): 計算に使用するデータの範囲（必須）- 第2引数 (
C3:C7): 計算に使用するデータの範囲（オプション）- 第3,4,...引数 : 計算に追加するデータの範囲（オプション）
引数の区切り記号で、演算を変えられる
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 セルに貼り付けると、 F列に条件に合うデータ数（カウント数）が出力されます。
データを書き換える場合は、 A, B列のデータを書き換えてください。 F列の数式を書き換えて下さい。
| A | B | C | D | E | F | G | |
| 1 | データ | 条件付きカウント | |||||
| 2 | X | Y | |||||
| 3 | a | 1 | (単一条件) | X="a" : | =SUMPRODUCT(--(A3:A8="a")) | 個 | |
| 4 | b | 1 | |||||
| 5 | c | 2 | |||||
| 6 | d | 2 | (複数条件) | X="a" AND Y=3 : | =SUMPRODUCT((A3:A8="a")*(B3:B8=3)) | 個 | |
| 7 | a | 3 | |||||
| 8 | b | 3 | X="a" OR X="b" : | =SUMPRODUCT((A3:A8="a")+(A3:A8="b")) | 個 | ||
| 9 | |||||||
| 10 | X="a" OR Y=3 : | =SUMPRODUCT(--((A3:A8="a")+(B3:B8=3)＞0)) | 個 | ||||
| 11 |
計算の仕組み
本サンプル中では、以下の4パターンの条件設定でデータ数をカウントしています。
- 単一条件
- AND条件 : 単一条件を積
*で結合 - OR条件（重複なし）: 単一条件を和
+で結合 - OR条件（重複あり）: 単一条件を和
+で結合し、条件>0を追加
条件設定は 配列数式 内で行っているため、
データ数をカウントするロジック自体は、次の2つで構成されています。
- 計算1. 配列数式 で条件式を設定し、条件に合う要素のみ
1（他は0）の配列を作成 - 計算2. 1の配列を
SUMPRODUCT関数に渡し、合計値を取得
以下では、各4つのパターンそれぞれの算出手順（主に計算1）について解説します。
単一条件のとき
- 計算式：
=SUMPRODUCT(--(A3:A8="a")) - 設定されている条件：
A3:A8配列の中でaのもの
上記の条件を設定した 配列数式 を、SUMPRODUCT関数に渡しています。
| A | B | C | D | E | F | G | H | I | J | |
| 1 | データ | |||||||||
| 2 | X | Y | A3:A8="a" | → | --(A3:A8="a") | → | SUMPRODUCT(--(A3:A8="a")) | |||
| 3 | a | 1 | TRUE | 1 | 2 | |||||
| 4 | b | 1 | FALSE | 0 | ||||||
| 5 | c | 2 | FALSE | 0 | ||||||
| 6 | d | 2 | FALSE | 0 | ||||||
| 7 | a | 3 | TRUE | 1 | ||||||
| 8 | b | 3 | FALSE | 0 | ||||||
| 9 | ||||||||||
| 10 |
論理値から数値への変換（-- または 1* の意味）
SUMPRODUCT関数は、受け取った配列要素が 論理値 の場合、正しく認識しません。
そのため受け渡す直前に、 論理値 を 数値 に変換する必要があります。比較演算（条件式）の結果は論理値となり、数式演算（四則演算）の結果は数値となります。
これを利用し、論理値を数値に変換する代表的な方法として以下の2つがあります：
- 前に
--を付ける : の乗算を行う- 前に
1*/ 後に*1を付ける : の乗算を行う
論理値と数値の対応関係
論理値（
TRUE/FALSE）は、以下の数値に対応します。
TRUE=1FALSE=0
AND条件のとき
- 計算式：
=SUMPRODUCT((A3:A8="a")*(B3:B8=3)) - 設定されている条件：
A3:A8配列の中でa、かつ、B3:B8配列の中で3のもの
配列数式 上で、2つの条件を「かつ」で結ぶには、 乗算 を使います。 SUMPRODUCT関数に渡しています。
| A | B | C | D | E | F | G | H | I | J | K | |
| 1 | データ | ||||||||||
| 2 | X | Y | A3:A8="a" | B3:B8=3 | → | (A3:A8="a")*(B3:B8=3) | → | SUMPRODUCT((A3:A8="a")*(B3:B8=3)) | |||
| 3 | a | 1 | TRUE | FALSE | 0 | 1 | |||||
| 4 | b | 1 | FALSE | FALSE | 0 | ||||||
| 5 | c | 2 | FALSE | FALSE | 0 | ||||||
| 6 | d | 2 | FALSE | FALSE | 0 | ||||||
| 7 | a | 3 | TRUE | TRUE | 1 | ||||||
| 8 | b | 3 | FALSE | TRUE | 0 | ||||||
| 9 | |||||||||||
| 10 |
OR条件（重複なし）のとき
- 計算式：
=SUMPRODUCT((A3:A8="a")+(A3:A8="b")) - 設定されている条件：
A3:A8配列の中でa、または、A3:A8配列の中でbのもの
配列数式 上で、重複のない2つの条件を「または」で結ぶには、 和算 を使います。 A3:A8配列の中で a かつ b のもの」はないため、2つの条件に重複はありません。 SUMPRODUCT関数に渡しています。
| A | B | C | D | E | F | G | H | I | J | K | |
| 1 | データ | ||||||||||
| 2 | X | Y | A3:A8="a" | A3:A8="b" | → | (A3:A8="a")+(A3:A8="b") | → | SUMPRODUCT((A3:A8="a")+(A3:A8="b")) | |||
| 3 | a | 1 | TRUE | FALSE | 1 | 4 | |||||
| 4 | b | 1 | FALSE | TRUE | 1 | ||||||
| 5 | c | 2 | FALSE | FALSE | 0 | ||||||
| 6 | d | 2 | FALSE | FALSE | 0 | ||||||
| 7 | a | 3 | TRUE | FALSE | 1 | ||||||
| 8 | b | 3 | FALSE | TRUE | 1 | ||||||
| 9 | |||||||||||
| 10 |
OR条件（重複あり）のとき
- 計算式：
=SUMPRODUCT(--((A3:A8="a")+(B3:B8=3)>0)) - 設定されている条件：
A3:A8配列の中でa、または、B3:B8配列の中で3のもの
配列数式 上で、重複のある2つの条件を「または」で結ぶには、 和算 + >0 の論理演算 を使います。 A3:A8配列の中で a かつ B3:B8配列の中で 3 のもの」があり、2つの条件に重複が存在します。 SUMPRODUCT関数に渡しています。
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |
| 1 | データ | ||||||||||||||
| 2 | X | Y | A3:A8="a" | B3:B8=3 | → | (A3:A8="a")+(B3:B8=3) | → | (A3:A8="a")+(B3:B8=3)＞0 | → | --((A3:A8="a")+(B3:B8=3)＞0)) | → | SUMPRODUCT(--((A3:A8="a")+(B3:B8=3)＞>0)) | |||
| 3 | a | 1 | TRUE | FALSE | 1 | TRUE | 1 | 3 | |||||||
| 4 | b | 1 | FALSE | FALSE | 0 | FALSE | 0 | ||||||||
| 5 | c | 2 | FALSE | FALSE | 0 | FALSE | 0 | ||||||||
| 6 | d | 2 | FALSE | FALSE | 0 | FALSE | 0 | ||||||||
| 7 | a | 3 | TRUE | TRUE | 2 | TRUE | 1 | ||||||||
| 8 | b | 3 | FALSE | TRUE | 1 | TRUE | 1 | ||||||||
| 9 | |||||||||||||||
| 10 |