【Excel】SUMPRODUCT関数でセル値を除外条件にして件数カウントや合計を求める。

皆さんはこんな経験はありませんか？「複数の条件を除外して件数をカウントしたいけど」「除外する条件をセル指定では出来ない」「除外条件を可変して条件に合った値の合計を求めたい」

そう、COUNTIF関数、COUNTIFS関数やSUMIF関数では検索条件を不等号にする時には実数で指定する必要があります。つまり,セル指定の不等号は使用できません（<>F2や>C5などは使えない）

実はSUMPRODUCT関数でこれが可能になります。

SUMPRODUCT関数の基本的使い方

SUMPRODUCT関数は配列の積の合計を出す関数で、一般的には=SUMPRODUCT(配列,配列･･･)となっています。

上の表の赤枠部分の式は =SUMPRODUCT(C2:C7,D2:D7) で、C列のそれぞれのセルとD列のそれぞれのセルを掛け算した値を合計しています。

この様な一般的な使い方では、横計の計算をすることなく総計を算出できるのがSUMPRODUCT関数で、特に横計をするセルを追加出来ない場合には有効です。

しかし、SUMPRODUCT関数の使い方はこれだけではありません。SUMPRODUCT関数の使い方の応用として、COUNTIFS関数やSUMIF(S)関数と同じ目的でしかもこの２つの関数には出来ないことが出来てしまいます。

COUNTIFS関数の引数は =COUNTIFS(検索範囲1,検索値1,検索範囲2,検索値2･･･)で、ルールとしてそれぞれの検索範囲の行もしくは列は相対位置である必要があります。例えば縦に検索する場合は同じ行数にする必要があります。

また、検索値はセル指定も可能ですが、不等号(<>)を使う時はダブルクォーテーション(“)で囲う必要があるのとこの時はセル指定はできません。

つまり「〇〇を除いた件数をカウントしたい」という場合には計算式内に直接値を入れる必要があるということです。そして、検索値を変更したい時には計算式を変更する必要があります。

除外したい値が常に一定であればそれでも問題は無いのでしょうが、もし都度変更したいという場合には非常に面倒に感じるでしょう。

これを解決出来るのが実はSUMPRODUCT関数です。

まずは、緑枠の部分ですが全都道府県から東北地域と北海道を除いた都府県数をカウントしています。

式は多少複雑かもしれませんが配列と不等号を使ってセル指定をしていてルールとしては検索範囲と除外条件を括弧で囲うことと、それぞれの範囲と条件の間にアスタリスク(*)を入れること。またCOUNTIFSと同様に検索範囲は全て同じ行指定とします。

次に複数の条件にあった合計を求めたい時に使うSUMIFS関数ですが、指定した条件を除いた部分の合計を求める使い方です。

上表の赤枠と青枠がその例で赤枠は『東北』と『北海道』を除いた人口の合計で、青枠は都道府県の人口で100万人超でかつ300万人未満の合計を求めています。

ちなみに除外する条件は可変出来る様になっていてビジネスシーンではよくあることではないでしょうか。

この式の特徴は検索範囲と検索条件を括弧で括ることと複数の場合はアスタリスク（＊）で区切ること、合計する範囲はカンマ(，)で区切ること、そして検索範囲は全て同じ行とすることです。