MENU

ExcelのIF+COUNTAで件数が出ない原因と解決策｜COUNTIFSで担当者別・ステータス別集計

Excel

Excelのテーブル集計で「IF+COUNTA」を使ったら、1つの数値が出ずにFALSEがずらっと並んだ…という経験はありませんか？本記事ではFinal_Report_2の担当者列・ステータス列を例に、原因（配列とスピル）と、COUNTIFSで担当者別・完了・Removedを正しく数える方法を解説します。

目次

IF／COUNTAで件数を出そうとして「FALSEが行ごとに並ぶ」症状

元データがExcelのテーブル Final_Report_2（構造化参照）として管理されていて、そこにAssigned To（担当者）列、Status（ステータス）列があるケースを想定します。別シートで担当者ごとの件数や、Completed（完了）・Removed（削除）などのステータス別件数をまとめたいとき、次の式を入れたら「数値が1つ返る」のではなく、結果が縦に展開されてしまうことがあります。

=IF(Final_Report_2[Assigned To]=A5, COUNTA(Final_Report_2[Status]))

具体的には、担当者列の行数ぶん結果が並び、条件に合わない行は FALSE（または0）になり、条件に合う行は同じ数値が繰り返される、といった見え方になります。Excel 365／Excel 2021以降の「動的配列」に慣れていないと、かなり戸惑いやすいポイントです。

なぜ起きるのか：条件式が「1つの判定」ではなく「配列」になっている

原因の中心は、式のこの部分です。

Final_Report_2[Assigned To]=A5

この比較は「担当者列の全行」と「A5セルの値」を照合します。つまり、判定結果は1つのTRUE/FALSEではなく、行数ぶんのTRUE/FALSEが並ぶ配列になります。

式（抜粋）Excelが内部で作るイメージ意味
Final_Report_2[Assigned To]担当者列の値が行数ぶんテーブル列全体（縦方向の配列）
Final_Report_2[Assigned To]=A5{TRUE;FALSE;TRUE;…}各行がA5と一致するかの判定結果

そしてIF関数は、条件（logical_test）が配列で渡されると、配列の要素ごとに判定を行い、その結果をスピル（自動展開）でセルに並べて返します。これが「行ごとにFALSEなどが並ぶ」直接の理由です。

IF+COUNTAが「担当者で絞った件数」にならない理由

もう1つ重要なのが、COUNTA(Final_Report_2[Status])の意味です。COUNTAは空でないセルの数を数える関数で、条件を見ません。つまり、この部分は「Status列の総件数（空白でない数）」であって、担当者A5に絞った件数ではありません。

結果として、Excelは次のような処理をしようとします。

  • 条件：{TRUE;FALSE;TRUE;…}（行ごとの配列）
  • TRUEの行：COUNTA(Final_Report_2[Status])（同じ総件数）
  • FALSEの行：FALSE

そのため、見た目として「総件数が繰り返される」「FALSEが混ざる」「1つの数値にならない」といった状態になります。

結論：条件付きの件数はCOUNTIFSで数える

やりたいことは「条件に合う行の件数を数える」なので、IF+COUNTAではなくCOUNTIFSが最短で安全です。COUNTIFSは複数条件のカウントに最適化されており、結果は1つの数値になります。

集計したい内容おすすめ式ポイント
担当者A5の件数=COUNTIFS(Final_Report_2[Assigned To], A5)担当者列がA5と一致する行を数える
Completed（完了）の件数=COUNTIFS(Final_Report_2[Status], "Completed")ステータス列がCompletedの行を数える
担当者A5のCompleted件数=COUNTIFS(Final_Report_2[Assigned To], A5, Final_Report_2[Status], "Completed")複数条件を「AND」で絞り込み
担当者A5のRemoved件数=COUNTIFS(Final_Report_2[Assigned To], A5, Final_Report_2[Status], "Removed")完了/削除などの内訳にそのまま応用

ここまでを押さえるだけで、担当者別の件数、全体の完了件数、削除件数などの「定番集計」はほぼ作れます。

COUNTIF／COUNTIFS／COUNTAの違い（迷ったときの早見表）

集計の式が複雑になりやすい原因の一つが「似た関数が多い」ことです。迷ったら、まず次の整理で考えると早いです。

関数数える対象条件典型例
COUNTA空でないセル指定不可入力済みの件数（総数）
COUNTIF条件に合うセル1条件StatusがCompletedの件数
COUNTIFS条件に合う行複数条件担当者×Statusで絞った件数

担当者とステータスのように「列をまたいで条件を組み合わせる」集計は、基本的にCOUNTIFSが第一選択になります。

担当者別の集計表を作る具体例

実務では、別シートに担当者一覧を縦に並べ、右側に各種件数を並べると運用が楽です。例えば次のような形です（A列に担当者名、B列以降に集計）。

列見出し例入れる式（先頭行が5行目の例）
A担当者（担当者名を入力／一覧貼り付け）
B全件=COUNTIFS(Final_Report_2[Assigned To], $A5)
CCompleted=COUNTIFS(Final_Report_2[Assigned To], $A5, Final_Report_2[Status], "Completed")
DRemoved=COUNTIFS(Final_Report_2[Assigned To], $A5, Final_Report_2[Status], "Removed")
E未完（Completed以外）=COUNTIFS(Final_Report_2[Assigned To], $A5, Final_Report_2[Status], "<>Completed")

ポイントは、A列の担当者セル参照を$A5のように列固定で書き、下にコピーしても担当者名の列がずれないようにすることです。

また、「未完」をより厳密にしたい場合は、Removedも除外するなど運用ルールに合わせます。

=COUNTIFS(
  Final_Report_2[Assigned To], $A5,
  Final_Report_2[Status], "<>Completed",
  Final_Report_2[Status], "<>Removed"
)

「全体で何件完了しているか」を別セルで出す

担当者別だけでなく、全体の完了件数・削除件数を上部に置くと、レポートとして読みやすくなります。

集計式使いどころ
全体の総件数（Statusが空でない）=COUNTIFS(Final_Report_2[Status], "<>")Statusが空白の行を除外したいとき
全体のCompleted件数=COUNTIFS(Final_Report_2[Status], "Completed")完了の総数を即表示
全体のRemoved件数=COUNTIFS(Final_Report_2[Status], "Removed")削除の総数を即表示

複数ステータスをまとめて数える（Excel 365向けの実務テク）

運用によっては「Completed」「Closed」「Done」を同じ“完了扱い”にしたいなど、複数値をまとめてカウントしたいことがあります。その場合、動的配列のExcelでは配列定数を使って、最後にSUMで合算するのが手軽です。

例：StatusがCompletedまたはClosedの件数

=SUM(COUNTIFS(Final_Report_2[Status], {"Completed","Closed"}))

例：担当者A5で、StatusがCompletedまたはClosedの件数

=SUM(COUNTIFS(
  Final_Report_2[Assigned To], A5,
  Final_Report_2[Status], {"Completed","Closed"}
))

「COUNTIFSが配列を返す → SUMで1つにする」という発想は、複数条件のバリエーションを増やしたいときに役立ちます。

FILTERで対象行を抽出して、件数と一覧を同時に扱う

集計だけでなく「どの行がカウント対象なのか」をその場で確認したいなら、FILTER関数が便利です。まずは対象行の一覧を別エリアに表示できます。

例：担当者A5かつCompletedの行を抽出（一覧表示）

=FILTER(Final_Report_2,
  (Final_Report_2[Assigned To]=A5)*
  (Final_Report_2[Status]="Completed")
)

件数だけ欲しい場合は、抽出結果の行数をROWSで数えます。

=ROWS(FILTER(Final_Report_2[Status],
  (Final_Report_2[Assigned To]=A5)*
  (Final_Report_2[Status]="Completed")
))

COUNTIFSより式は長くなりますが、「抽出結果を目視できる」ため、集計の検証や監査に向きます。集計表の横に“根拠一覧”を出すと、レポートの説得力が上がります。

COUNTIFSがうまく合わないときのチェックポイント

COUNTIFSは堅牢ですが、元データ側の表記ゆれがあると想定より少なく（または多く）数えます。実務でよくある詰まりどころと、現場で効く対処をまとめます。

症状よくある原因確認方法対処例
Completedが0件になるステータスが「Completed 」など末尾スペース付き=LEN([@Status]) と見た目の文字数差を確認入力規則で統一、または元データ側でTRIM適用
担当者別の件数が合わない担当者名の表記ゆれ（全角半角、スペース、略称）担当者列を並べ替え／重複を目視マスタ（担当者一覧）から選択入力にする
Statusが空白の行が混ざる未入力行や作業中の行が存在フィルターで空白を抽出"<>" 条件で空白を除外して集計
大文字小文字を区別したいCOUNTIFSは基本的に大小を区別しない同じに見えるが別物として扱いたいケースEXACTを使った配列集計（後述）

特に「末尾スペース」「表記ゆれ」は、集計の精度を一気に下げます。集計式を複雑にする前に、入力規則（ドロップダウン）やマスタからの選択で、元データを整えるほうがトラブルが減ります。

IFを使うなら：配列を「合計」して1つの数値にする

「IFがダメ」というより、IFの結果が配列なら、最後に集計（SUMなど）をしないと1つの数字にならない、というのが本質です。COUNTIFSが使えない特殊要件（大小文字の区別、部分一致の厳密条件、複雑なロジックなど）があるときは、次の考え方が役に立ちます。

担当者A5の件数をIFで数える（考え方）

担当者列がA5なら1、違うなら0を返し、それを合計します。

=SUM(IF(Final_Report_2[Assigned To]=A5, 1, 0))

動的配列のExcelではそのまま動作しますが、古いExcelでは配列数式（Ctrl+Shift+Enter）が必要になる場合があります。現場でファイル共有があるときは、互換性も踏まえてCOUNTIFSを選ぶほうが無難です。

SUMPRODUCTで条件付き件数を数える（互換性が高い）

SUMPRODUCTは配列の掛け算→合計で件数を出せるため、配列数式の入力操作に依存しにくいのが利点です。

=SUMPRODUCT((Final_Report_2[Assigned To]=A5)*1)

Completed件数まで含めるなら、条件を2つ掛けます。

=SUMPRODUCT((Final_Report_2[Assigned To]=A5)*(Final_Report_2[Status]="Completed"))

「Statusが空でないものだけ」を数えたいなら、空白判定を条件にします。

=SUMPRODUCT((Final_Report_2[Assigned To]=A5)*(Final_Report_2[Status]<>""))

大小文字を区別するカウント（EXACT＋SUMPRODUCT）

COUNTIFSは多くのケースで十分ですが、例えば「abc」と「ABC」を別扱いしたいなど、大小文字を区別したい要件もあります。その場合はEXACTで一致判定を作って合計します。

=SUMPRODUCT(--EXACT(Final_Report_2[Status], "Completed"))

担当者条件も合わせるならこう書けます。

=SUMPRODUCT(--(Final_Report_2[Assigned To]=A5), --EXACT(Final_Report_2[Status], "Completed"))

このように「配列の真偽値 → 0/1化 → 合計」という流れを覚えておくと、COUNTIFSでは表現しづらい集計にも対応できます。

スピルを“直す”のではなく“意味を変える”のが近道

スピル自体はExcelの正常な動きです。問題は「本当は集計したいのに、行ごとの判定を返す式を書いてしまった」ことにあります。@（暗黙の交差）で無理やり1つに潰す方法もありますが、集計の意図が隠れて誤解を生みやすいため、基本はおすすめしません。

件数を出したいならCOUNTIFS、一覧を出したいならFILTER、行ごとの判定が欲しいならIF、というように、目的に合う関数へ切り替えるほうが結果的に早く、保守もしやすくなります。

逆にIFが向くケース：行ごとの判定列を作りたいとき

今回のような「1つの数値に集計する」用途ではCOUNTIFSが適任ですが、IFが活躍する場面もあります。例えば元データ側に「この行は集計対象か？」というフラグ列を作るなど、行ごとの結果が必要なときです。

Final_Report_2テーブル内に「対象」列を追加して、A5の担当者に一致したら「対象」と表示する例です（テーブル内なので[@列名]が使えます）。

=IF([@[Assigned To]]=$A$5, "対象", "")

このように「行に対する判定」を作っておくと、フィルターや条件付き書式とも相性が良く、集計表だけでなく運用面も楽になります。

集計が大きくなったら：ピボットテーブルで一気に可視化

担当者×ステータスのクロス集計を繰り返し見るなら、数式だけで頑張るより、ピボットテーブルのほうが早く・見やすく・ミスが減ります。Final_Report_2がテーブル化されている時点で、ピボットの準備はほぼできています。

  1. Final_Report_2の任意のセルをクリック
  2. ［挿入］→［ピボットテーブル］を選択
  3. 行にAssigned To、列にStatusを配置
  4. 値にStatus（またはID列）を入れ、集計方法を「個数」にする

これで「担当者別の総数」「Completedの内訳」「Removedの内訳」が一つの表で見えるようになります。さらに日付列があるなら、月別・週別の進捗まで簡単に拡張できます。

まとめ：条件付き件数はCOUNTIFS、配列の行判定はIF

  • Final_Report_2[Assigned To]=A5は列全体を比較するため、結果はTRUE/FALSEの配列になる
  • 動的配列のExcelでは、配列を返す式はスピルして行ごとに展開される
  • COUNTAは条件を見ず「空でない数」を数えるだけなので、担当者で絞った件数にならない
  • 担当者別・ステータス別の件数はCOUNTIFSが最短で安全
  • どうしてもIFを使うなら、最後にSUMやSUMPRODUCTで1つの数値に集計する

Excelの集計は、式の見た目よりも「その関数が返す値が1つなのか、配列なのか」を意識すると一気に安定します。まずはCOUNTIFSで基本形を作り、要件が複雑になったらSUMPRODUCTやFILTER、さらに全体設計としてピボットテーブルへ、という流れが実務では失敗しにくい選択です。

Excel
Excel 動的配列 COUNTIFS 構造化参照

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次