数式/関数：フィルタをかけた範囲から条件指定で集計する

フィルタで抽出した範囲の個数や合計を計算するのは、SUBTLTAL関数や AGGREGATE関数を使えば簡単にできる。ただこの絞った範囲の中で COUNTIF関数や SUMIF関数が使いたいといい始める人が時々いる。

例えばこういうパターン。「A」で絞った場合「3」となるのが正解。

ただこれ、古いバージョンで数式だけでやろうとすると処理が激重くなる。なので「フィルタで絞るところから、全部数式でやればいいんじゃない？　というかクエリでやったら？」とやんわり別案をお勧めしても、大抵の人は納得してくれない。

そこで次点としてピボットテーブル案をお勧めしてみる。「フィルタ＋数式」を「ピボットテーブル＋数式」にするだけだし。

行フィールドに「大分類」「中分類」「小分類」を入れて（集計フィールドには何も入れない）、レポートのレイアウトを「表形式で表示」にしてやれば準備完了。

C列全体をカウントしてから見出しの分を引いてやればいい。

=COUNTA(C:C)-1

これだけ。

これだけなんだけど、やっぱり「ピボットテーブルを作りたくない」とか「更新するのが面倒」とかいってくる人がいる。「フィルタで絞って数式で条件計算」でないと納得できないらしい。

というわけでしょうがないので数式案（Microsoft365）。

=ROWS(UNIQUE(FILTER(テーブル1,
BYROW(テーブル1[大分類],LAMBDA(a,(a<>"")*(SUBTOTAL(103,a)))))))

フィルタ限定なら SUBTOTALの第一引数は「3」でもよい。

因みにクエリでやるなら、予めテーブル1を接続のみで読み込んであるとして下記の通り。

let
    ソース = テーブル1,
    行のグループ化 = Table.Group(
        テーブル1, {"大分類"},
        {"件数", each Table.RowCount(Table.Distinct(_)), Int64.Type}
    )
in
    行のグループ化

超簡単。