COUNTIFSで複数条件に基づいて数える

COUNTIFSは、複数の条件を同時に満たす行数をカウントします。COUNTIFが「Eastでの売上はいくつか」という問いに答えるのに対し、COUNTIFSは「1月に発生したEastの売上はいくつか」という問いに答えます。

COUNTIFSは加算も平均もせず、すべてのテストに合格した行を単純に数えます。そのため、「新規顧客からの100ドルを超える注文はいくつか」のような問いに最適です。

構造を学び、実際に使ってみましょう。

COUNTIFSにはsum_rangeがありません。値を加算するのではなく、テストする範囲と、その範囲に対してテストする条件だけが必要だからです。

• criteria_range1、criteria1 — 最初のテスト
• criteria_range2、criteria2 — 2つ目のテスト
• ...以下同様に、最大127個のペア

すべての引数は、整然とした範囲と条件のペアで指定します。結果は、すべての条件が真である行の数を示す単一の整数です。

同じ売上表を使います。列AはRegion、列BはMonth、列CはAmountです。Eastで、かつ1月だった売上がいくつあるかを数えてみましょう。

この数式に金額の列はありません。金額を合計するのではなく、行を数えるためです。各ペアによってカウント対象がさらに絞り込まれます。

SUMIFSと同じように、COUNTIFSでも数値のテストに引用符で囲んだ比較演算子を使えます。

• ">100" — 100を超える値
• ">=18" — 18以上の値
• "<>" — 空でないセル

ここでは、金額が100より大きいEast地域の行を数えます。金額の列はsum_rangeではなく、criteria_rangeとして指定されていることに注意してください。

よくある要件の1つに、2つの数値の間にある値をカウントすることがあります。これは1つの条件ではできません。下限と上限を指定するために、同じ列を2回指定します。

ここでは、50以上かつ200以下の金額を数えます。両方の条件が列Cを参照し、COUNTIFSはAND論理を使うため、この範囲内にある行だけがカウントされます。

範囲を動的にするには、下限と上限をセルに保存し、演算子を & で連結します。下限値を F1 に、上限値を F2 に入力します。

">="&F1 は、F1以上という条件を作ります。これにより、ユーザーは新しい上限と下限を入力するだけで、数式自体を編集せずに更新された件数をすぐに確認できます。

データ品質を確認する際に便利な特殊な条件が2つあります。

• "<>" は空でないセルをカウントします
• "=" または "" は空のセルをカウントします

この数式では、East地域の行のうち、担当者が入力されている行（列Dが空白でない行）の数をカウントします。欠損データをすばやく見つける方法です。

テキストの条件では、SUMIFSと同じワイルドカードを使えます。任意の文字列には * を、1文字には ? を使います。

この方法では、担当者名がMで始まるすべての行をカウントします。Maria、Mike、Mohammedはいずれも一致します。ワイルドカードはテキストに対してのみ機能し、数値に対しては無視されます。

SUMIFSと同じサイズのルールが適用されます。すべてのcriteria_rangeは同じ形状でなければなりません。一方の範囲が A2:A100 で、もう一方が B2:B50 だと、COUNTIFSは #VALUE! エラーを返します。

A:A や B:B のような列全体の参照は自動的に同じサイズになるため、criteria_rangeにはエラーを防ぎやすい選択肢としてよく使われます。

COUNTIFSは多くのダッシュボードを支える仕組みです。列Fに地域名を縦に並べ、1行目に月名を横に並べます。その後、複合参照を使って各組み合わせをカウントし、数式をグリッド全体に入力します。

$A:$A で列を固定し、検索するセルを $F2 / G$1 で固定すると、数式をコピーしてクロス集計表全体に件数を入力できます。

関数の使い分けを整理しましょう。COUNTIFは1つの range, criteria ペアを受け取り、COUNTIFSは1つ以上のペアを受け取ります。SUMIFSとは異なり、どちらにも個別の結果範囲はありません。どちらも単純にカウントします。

条件が複数ある場合は、COUNTIFSを使いましょう。条件が1つだけでもCOUNTIFSを使い、SUM、COUNT、AVERAGEで一貫したパターンを覚えるだけにする人は多くいます。

COUNTIFSとSUMIFSの違いについて考えてみましょう。

複数の条件にわたって行をカウントできるようになりました。

• COUNTIFS(range1, crit1, range2, crit2, ...) — sum_rangeはなく、テストのペアだけを指定します。
• 同じ列を2つの境界値とともに繰り返し指定すると、範囲内の値をカウントできます。
• 空白でないセルには "<>" を使い、セルを基準にした上限と下限は & で連結します。
• すべての範囲を同じサイズにする必要があります。

次は、AVERAGEIFSを使って複数の条件に合う値の平均を求めます。