エクセルでフィルタした結果に対して関数だけでCOUNTIFする方法

エクセル便利ですね!
データを見る時にフィルタは結構多用すると思いますが、
ファイルをしたあとのデータについては関数が思ったように動かないものがあります。


以下のような表があったときに分類Aのステータスを数える場合を考えます。
f:id:GARAPON:20160128195112p:plain

フィルターがなければCOUNTIFで数えられるのですが、COUNTIFはフィルターを考慮してくれないためフィルタしても数が変わりません。
マクロを使ったりワークセルを作ったりすればできるのですが、常に自動計算してくれると考えるとやっぱり関数だけで計算しきとしてつくりたい!
とおもってたら以下のような式でできました。

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E5,ROW(INDIRECT("1:"&ROWS(E6:E11))),))*(E6:E11=B2))

こんな感じですね。

金額が100円以上でフィルタ

f:id:GARAPON:20160128195120p:plain

チームACでフィルタ

f:id:GARAPON:20160128195123p:plain

ちゃんとステータスの済、未が数えられていますね。

なぜ動くのか

f:id:GARAPON:20160128195117p:plain

何をしているかというとSUMPRODUCTの*より前半

SUBTOTAL(3,OFFSET(E5,ROW(INDIRECT("1:"&ROWS(E6:E11))),))

の部分で数字が全部1の仮想配列を作り出し、その配列とD列の条件配列「E6:E11=B2」(「E6:E11="未"」と同じ)を掛け合わせ、その合計を取得しています。
条件配列は条件に合致したもののみが評価されるので、結果として条件が合う行分1が加算され、個数を数えることができます。

利用する場合は以下を参考に適宜書き換えてご利用ください。
f:id:GARAPON:20160128195117p:plain

参考

エクセルの配列数式の考え方は以下がとても参考になります。
pc.nikkeibp.co.jp