Excel で範囲内の x または y のいずれかを含むセルをカウントする

いずれかの値を含むセルをカウントする際、一部の Excel ユーザーは2 つの COUNTIF 関数を単純に加算しますが、この方法では「x または y」（OR 条件）ではなく、「x と y の両方」（AND 条件）を満たすセルがカウントされてしまいます。二重カウントを回避するために、このチュートリアルでは SUMPRODUCT 関数を使った数式を詳しく解説し、Excel で指定範囲内に x または y のいずれかを含むセルを正確にカウントする方法をご紹介します。

Excel で範囲内の x または y のいずれかを含むセルをカウントする方法

以下のスクリーンショットのように、データ範囲 B3:B9 に「KTE」または「KTO」のいずれかを含むセルがいくつあるかをカウントするには、次の数式をご利用ください。

汎用数式

=SUMPRODUCT(--((ISNUMBER(FIND("criteria1",rng)) + ISNUMBER(FIND("criteria2",rng)))>0))

引数

この数式の使用方法

1．結果を表示する空白セルを選択してください。

2．以下の数式を入力して、Enterキーを押すと、結果が表示されます。

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

これらの数式の動作原理

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

• 1. FIND(D3,B3:B9)：FIND 関数は、D3 の値「KTE」が指定された範囲（B3:B9）内に存在するかどうかをチェックし、次のような配列を返します：{1、#VALUE！、#VALUE！、#VALUE！、#VALUE！、1、#VALUE！}。
  この配列では、2 つの「1」がリスト B3:B9 の最初と下から2 番目のセルに「KTE」が含まれていることを示しており、「#VALUE！」はその他のセルに「KTE」が見つからないことを意味します。
• 2. ISNUMBER{1,#VALUE!, #VALUE!, #VALUE!, #VALUE!,1, #VALUE!}ISNUMBER 関数は、配列内で数値に遭遇すると TRUE を返し、エラーに遭遇すると FALSE を返し、最終的に {TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE} という新しい配列を生成します。
• 3. FIND(D4,B3:B9)この FIND 関数も同様に、D4 の値「KTO」が制限された範囲（B3:B9）内に存在するかどうかをチェックし、次のような配列を返します：{#VALUE！、1、#VALUE！、#VALUE！、#VALUE！、#VALUE！、#VALUE！}。
• 4. ISNUMBER{#VALUE!,1,#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!}ISNUMBER 関数は、配列内で数値に遭遇すると TRUE を返し、エラーに遭遇すると FALSE を返し、最終的に {FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE} という新しい配列を生成します。
• 5. {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE} + {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}ここで、2 つの配列から {1;1;0;0;0;1;0} という結果が得られます。
• 6. {1;1;0;0;0;1;0}>0ここで、配列内の各数値が 0 より大きいかどうかを比較し、{TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE} という結果が得られます。
• 7. --({TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE})この2 つのマイナス記号により、「TRUE」は1 に、「FALSE」は0 に変換され、新しい配列 {1;1;0;0;0;1;0} が得られます。
• 8. =SUMPRODUCT({1;1;0;0;0;1;0})SUMPRODUCT 関数は配列内のすべての数値を合計し、このケースでは最終結果として 3 を返します。

関連関数

関連数式