Excel で複数の条件に対して OR 論理を使用した COUNTIFS

通常、Excel では COUNTIFS 関数を使って、AND 条件に基づいてセルをカウントできます。しかし、単一の列またはセル範囲から複数の値をカウントしたいと思ったことはありませんか？これは、OR 条件で複数の条件を満たすセルを数えることを意味します。そんなときは、SUM 関数と COUNTIFS 関数を組み合わせるか、SUMPRODUCT 関数を使うと簡単に実現できます。

• Excel で OR 条件でセルをカウントする
  • SUM 関数と COUNTIFS 関数を使用して、OR 条件でセルをカウントする
  • SUMPRODUCT 関数を使用して、OR 条件でセルをカウントする
• Excel で複数の OR 条件セットでセルをカウントする
  • SUM 関数と COUNTIFS 関数を使用して、2 つの OR 条件セットでセルをカウントする
  • SUMPRODUCT 関数を使用して、複数の OR 条件セットでセルをカウントする

Excel で OR 条件でセルをカウントする

たとえば、以下のようなスクリーンショットに示すデータ範囲があるとします。このとき、「Pencil」または「Ruler」である製品の個数をカウントしたい場合、Excel でこのタスクを解決するための数式が2 つあります。

SUM 関数と COUNTIFS 関数を使用して、OR 条件でセルをカウントする

Excel で複数の OR 条件に基づいてカウントするには、配列定数を組み合わせて SUM 関数と COUNTIFS 関数を使用できます。一般的な構文は次のとおりです：

• range：データ範囲には、セルをカウントするための基準が含まれます。
• criterion1, criterion2, criterion3…：セルをカウントする際の条件です。

「Pencil」または「Ruler」の製品がいくつあるかをカウントするには、以下の数式を空白セルにコピーまたは入力し、Enterキーを押して結果を表示してください。

数式の説明：

=SUM(COUNTIFS(B2:B13,{「Pencil」,「Ruler」}))

• {「Pencil」,「Ruler」}：まず、「{"Pencil"、 "Ruler"}」のように、すべての条件を配列定数にまとめる必要があります。項目はカンマで区切ります。
• COUNTIFS(B2:B13,{「Pencil」,「Ruler」})：この COUNTIFS 関数は、「Pencil」と「Ruler」をそれぞれ個別にカウントし、結果は次のようになります：{2,3}。
• SUM(COUNTIFS(B2:B13,{「Pencil」,「Ruler」}))=SUM({2,3})：最後に、この SUM 関数が配列内のすべての値を合計し、結果として5 を返します。

ヒント：条件にはセル参照も使用できます。以下の配列数式を入力し、Ctrl + Shift + Enterキーを同時に押して、正しい結果を取得してください。

SUMPRODUCT 関数を使用して、OR 条件でセルをカウントする

ここでは、SUMPRODUCT 関数を使ったもう一つの数式をご紹介します。これも OR 条件でセルをカウントする際に役立ちます。一般的な構文は次のとおりです：

• range：データ範囲は、セルをカウントする基準が含まれる範囲です。
• criterion1, criterion2, criterion3…：セルをカウントする際に使用する条件です。

以下の数式を空白セルにコピーまたは入力し、Enterキーを押して結果を表示してください。

数式の説明：

=SUMPRODUCT(1*(B2:B13={「Pencil」,「Ruler」}))

• B2:B13={「Pencil」,「Ruler」}：この式は、セル範囲 B2:B13 内の各セルを「Pencil」と「Ruler」の各条件と比較します。条件に一致する場合は TRUE、そうでない場合は FALSE が表示され、結果は次のようになります：{TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE}。
• 1*(B2:B13={「Pencil」,「Ruler」})：乗算によって、論理値（TRUE および FALSE）がそれぞれ1 と0 に変換され、結果は次のようになります：{1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}。
• SUMPRODUCT(1*(B2:B13={「Pencil」,「Ruler」}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0})：最後に、SUMPRODUCT 関数が配列内のすべての数値を合計し、結果として5 を得ます。

Excel で複数の OR 条件セットでセルをカウントする

場合によっては、2 つ以上の OR 条件セットに基づいてセルをカウントする必要があるかもしれません。その際は、配列定数を活用した SUM 関数と COUNTIFS 関数の組み合わせか、ISNUMBER 関数と MATCH 関数を組み合わせた SUMPRODUCT 関数のいずれかをご利用いただけます。

SUM 関数と COUNTIFS 関数を使用して、2 つの OR 条件セットでセルをカウントする

OR 条件が2 セットだけの場合は、COUNTIFS 数式に別の配列定数を追加するだけで対応できます。

たとえば、次のようなスクリーンショットのデータ範囲があるとします。このとき、「Pencil」または「Ruler」を注文していて、かつ金額が200 である人物の数をカウントしたい場合です。

以下の数式をセルに入力またはコピーして、Enterキーを押すと、結果が表示されます（スクリーンショット参照）。

：この数式では、2 番目の配列定数にセミコロンを使って縦方向の配列を作成する必要があります。

SUMPRODUCT 関数を使用して、複数の OR 条件セットでセルをカウントする

上記の数式は、OR 条件が2 セットの場合にのみ機能します。さらに多くの条件でカウントする必要がある場合は、ISNUMBER 関数と MATCH 関数を組み合わせた複雑な SUMPRODUCT 数式が役立ちます。

以下のデータを例にとると、「Pencil」または「Ruler」を注文し、かつステータスが「Delivered」または「In transit」で、署名者が「Bob」または「Eko」である注文の件数をカウントするには、複雑な数式を適用する必要があります。

以下の数式を空白セルにコピーまたは入力し、Enterキーを押して計算結果を表示してください（スクリーンショット参照）。

数式の説明：

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{「Pencil」,「Ruler」},0))*ISNUMBER(MATCH(C2:C13,{「Delivered」,「In transit」},0))*ISNUMBER(MATCH(D2:D13,{「Bob」,「Eko」},0)))

ISNUMBER(MATCH(B2:B13,{「Pencil」,「Ruler」},0))：

• MATCH(B2:B13,{「Pencil」,「Ruler」},0)：この MATCH 関数は、範囲 B2:B13 内の各セルを指定された配列定数と比較します。一致する値が見つかった場合はその配列内での相対位置を返し、見つからない場合はエラー値を返します。そのため、結果は次のような配列になります：{1；#N/A；#N/A;2；#N/A;1；#N/A;2;1；#N/A;2；#N/A}。
• ISNUMBER(MATCH(B2:B13,{「Pencil」,「Ruler」},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A})：ISNUMBER 関数は、数値を TRUE に、エラー値を FALSE に変換します。その結果、次のような配列が得られます：{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}。

上記のロジックは、2 番目および3 番目の ISNUMBER 式にも適用できます。

SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{「Pencil」,「Ruler」},0))*ISNUMBER(MATCH(C2:C13,{「Delivered」,「In transit」},0))*ISNUMBER(MATCH(D2:D13,{「Bob」,「Eko」},0)))：

• 次に、これら3 つの配列が SUMPRODUCT 関数内で掛け合わされます。このとき、数学演算の過程で TRUE および FALSE の値は自動的にそれぞれ1 および0 に変換されます。式は次のようになります： SUMPRODUCT({1;0;0;1;0;1;0;1;1;0;1;0} * {1;1;0;0;1;1;0;1;1;0;1;1} * {1;0;0;0;0;0;0;1;0;0;1;0}) = SUMPRODUCT({1;0;0;0;0;0;0;1;0;0;1;0})。
• 最後に、SUMPRODUCT 関数が配列内のすべての数値を合計し、結果として3 を得ます。

使用された関連関数：

関連記事：