【関数】特定条件に一致するデータ個数をカウントできる「COUNTIFS」の使い方

Aさん

商品別や日別などの条件でレコード数をカウントしたいですが、COUNTAだと限界あります。。
こうした場合の集計に役立つ関数はありますか？

森田

その場合は、関数の「COUNTIFS」を活用すると良いですよ！
では、COUNTIFSの使い方について解説していきますね。

はじめに

この記事は関数の概要とCOUNTAの使い方を把握していることが前提です。

参考記事

関数の概要とCOUNTAの使い方については以下の記事をご参照ください。

【Excel基本】500種類以上の固有の計算/処理を自動化できる「関数」の前提知識や使い方まとめ | Excelを制する者は人生を制す
https://excel-master.net/common/excel-basic/worksheet-function-summary/
はじめにこの記事は数式の概要を把握していることが前提です。参考記事数式の概要の詳細は以下の記事をご参照ください。関数とは関数とは、数式の構成要素の1つであり、固有の計算/処理の機能がセットされた数式のことです。 …

【関数】指定範囲の空白以外のデータの個数をすべてカウントできる「COUNTA」 | Excelを制する者は人生を制す
https://excel-master.net/data-aggregation/worksheet-function-counta/
はじめにこの記事は関数の概要とSUMの使い方を把握していることが前提です。参考記事関数の概要とSUMの使 …

○○別でレコード数をカウントする場合は「COUNTIFS」が有効

実務では、データの特徴や傾向を把握するために、商品別や日別などの切り口で集計する機会は非常に多いもの。

こうした条件別の個数を集計する際、商品名や日付などの条件に一致するレコードのみでカウントすれば良いですが、頑張ればCOUNTAでも集計は可能です。

ただし、指定するセル数が多ければ多いほど、手作業は増え、誤ったセルを選択するリスクも増えてしまうため、この方法は非効率です。

こんな場合、関数の「COUNTIFS」を使うことで、条件に一致するレコードのみを対象とし、瞬時に個数を集計することが可能となります。
ちなみに、COUNTIFSは「カウント・イフス」と呼びます。

よって、条件別の個数を集計する場合はCOUNTIFSを使用していきましょう。

COUNTIFSの構文

COUNTIFSの構文は以下の通りです。

=COUNTIFS(検索条件範囲1,検索条件1,[検索条件範囲2,検索条件2],…)
特定の条件に一致するセルの個数を求めます。

引数名	必須	データ型	説明
検索条件範囲1	○	参照	「検索条件1」の検索対象のセル範囲を指定します。
検索条件1	○	すべて	「検索条件範囲1」内のどのセルを計算対象にするかの条件（値やセル番地など）を指定します。
検索条件範囲2	※省略可	参照	「検索条件2」の検索対象のセル範囲を指定します。
検索条件2	※省略可	すべて	「検索条件範囲2」内のどのセルを計算対象にするかの条件（値やセル番地など）を指定します。

引数[検索条件範囲n,検索条件n]は条件の数に応じてセットで追加できます。（最大127まで）
引数「検索条件範囲1」と引数「検索条件範囲2」以降で指定するセル数は一致している必要があります。（不一致の場合、「#VALUE!」のエラーが表示）

COUNTIFSの使用結果イメージ

COUNTIFSで条件別の個数を集計したイメージは以下の通りです。
今回は商品別の個数を集計しました。

ポイントは、引数の「検索条件範囲1」を各セルで同じ範囲に固定すること。
詳細は記事下部の「【参考】COUNTIFSの集計漏れを防ぐTIPS」を参照。

そして、引数「検索条件1」は1行ごとにスライドしています。

こうすることで、ベースとなる数式を以降のセルへ使い回せているわけですね。

参考記事

参照形式（絶対参照/相対参照）の詳細は以下の記事をご参照ください。

【Excel基本】数式を再利用し時短するために必須の相対参照/絶対参照/複合参照とは | Excelを制する者は人生を制す
https://excel-master.net/common/excel-basic/absolute-and-relative-reference/
関数の数式を直接入力できるようになってきました！もっと関数を実務で役立てていくために、他に覚えておいた方が良いことはありますか？？ …

COUNTIFSの数式の挿入手順

上記の結果を得るための手順は以下の通りです。

関数を挿入するセルを選択
「=cou」と入力
サジェストから「COUNTIFS」を選択し、「Tab」キーで確定
元データのシートへ移動
※今回は「注文テーブル」シート
条件の検索対象のセル範囲を選択
※今回は「商品名」列
コンマ（,）を入力
集計表のシートへ移動
※今回は「集計表」シート
条件となるセルを選択
※今回はA4セル
「Enter」キーで確定
1行目の数式をコピーし、以降のセルへペースト
※今回はB5~B12セルへペースト

手順②の際にIMEを半角英数モードにすること。
手順⑧も必要に応じて参照形式を変更すること。（今回は指定しなくともOK）
2種類以上の条件を設定したい場合、手順⑧の後にコンマ（,）を入力し、手順⑤~⑧を繰り返すこと。

【注意】COUNTIFSとCOUNTIFの使い分け問題

COUNTIFSと兄弟的な関数としてCOUNTIFがあります。

1~127種類の条件に対応できるCOUNTIFSと違い、COUNTIFは1種類の条件しか指定できません。

COUNTIFSで1種類の条件の集計もできる以上、原則は上位機能となるCOUNTIFSをメインで使うと良いでしょう。

その方が、後から条件を増やしたい場合の工数も最小限にできます。

逆に、COUNTIFでないといけない場合は、関数をセットする対象のExcelブックの拡張子が「.xls」の場合です。

COUNTIFSはExcel2007から登場した関数であり、同じくExcel2007から登場したExcelブックの拡張子の「.xlsx」と「.xlsm」でないと使えません。

よって、どうしても「.xls」のExcelブックで集計しないといけない場合は、昔から存在するCOUNTIFの方を使うこととなります。（条件が2種類以上の場合はSUMPRODUCT）
新機能が使え、ファイル容量も小さくなる「.xlsx」や「.xlsm」の方が断然メリットがあります。
現在「.xls」のExcelブックを使っている場合は、拡張子を「.xlsx」や「.xlsm」へ移行する調整を行うことがおすすめです。

参考記事

COUNTIFの詳細は以下の記事をご参照ください。

【関数】1種類の特定条件に一致するデータ個数をカウントできる「COUNTIF」 | Excelを制する者は人生を制す
https://excel-master.net/data-aggregation/countif-technique/
はじめにこの記事は関数の概要とSUMの使い方を把握していることが前提です。参考記事関数の概要とSUMの使 …

【参考】COUNTIFSの集計漏れを防ぐTIPS

COUNTIFSのリスクとしてよくあるのは、関数のセット後、元データへ追加されたレコードが集計範囲から漏れてしまうというケースです。

この原因は、手順⑤の際、セット時点での元データのレコード数で範囲指定しているためです。

よって、COUNTIFSのセット時点で、元データにレコードが追加されても問題ない範囲指定を行いましょう。（詳細は以下参照）

通常のセル範囲が対象

元データが通常のセル範囲の場合は、列単位で指定しましょう。

ワークシート上の列番号（アルファベット部分）をクリックすればOKです。

列全体を対象範囲にしているため、当然レコードが増えても計算対象に含まれます。

なお、COUNTIFSの数式を横方向にコピペする際、引数「検索条件範囲n」を横にスライドさせたくない（固定にしたい）場合は、列を絶対参照にしましょう。

テーブルの列が対象

元データがテーブルの場合は、普通にその列を指定すればOKです。

対象の列名が数式上にセットされ、テーブルにレコード追加されても、集計対象に含まれます。（集計対象範囲が自動拡張される）

特に指定がなければ、元データは事前にテーブルにしておくことがおすすめです。

【応用】COUNTIFSで完全一致以外の条件で集計するには

COUNTIFSの引数「検索条件n」は「完全一致」が原則です。

もし、完全一致以外の条件で集計したい場合は、「ワイルドカード」と「比較演算子」を活用すればOKです。

参考記事

COUNTIFSで完全一致以外の条件で集計する方法の詳細は以下の記事をご参照ください。

【関数】SUMIFSやCOUNTIFSで完全一致以外の条件で集計する方法 | Excelを制する者は人生を制す
https://excel-master.net/data-aggregation/sumifs_countifs-non-exact-match/
はじめにこの記事はSUMIFS・COUNTIFSの使い方を把握していることが前提です。参考記事 SUMIF …