Excel2010重複しないカウントとフィルタ結果

質問

Excel2010で、重複しない文字をカウントしつつ、オートフィルターで
抽出をかけた時には表示したデータをカウントする関数を作成したいのですが
うまくできません。

作成しているExcelの表には、オートフィルターが設定されています。
A列には複数名の人の名前が入っていて、名前には重複もあります。
オートフィルターでの抽出条件が指定されていない状態で
A列の重複しない人数を下記数式で出しています。

　=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))

オートフィルターで、例えばA列で3人の名前を抽出した場合に、
上記数式が入っているセルに「3」と表示をさせたいのですが
できますでしょうか？

できれば作業列を使用することなく、ダイレクトに該当セルに算出できる方法が
あれば知りたいです。

お分かりの方いらっしゃいましたらご教示頂けると助かります。
どうぞよろしくお願いいたします。

回答No.2

noname#203218 · ベストアンサー

質問の趣旨と違い回答をしてしまったようで、申し訳ありません。
お使いの関数でフィルターを掛ける掛けないで結果は変わらないと思うのですが、重複の削除を使用したのでセル範囲が変動する事でエラーが発生してるのでしょうか？

下記方法ではセル範囲可変となりますのでA列の最終行までカウントします（途中に空白セルが無い事が条件ですが）
=SUMPRODUCT(1/COUNTIF(OFFSET($A$1,,,COUNTA(A:A),1),OFFSET($A$1,,,COUNTA(A:A),1)))

趣旨違いの場合はご容赦下さい。

お礼

回答をくださりありがとうございました。
やはり1つのセルで算出するのは出来なさそうと判断し、作業列を使用することにしました。
ひとまず解決とさせて頂きます。
ご協力くださり感謝します。
どうもありがとうございました。

補足

何度も回答を頂き感謝いたします。
私の説明が悪かったようで申し訳ありません。
現在使用している関数に他の関数等を加えて、フィルターを掛ける掛けないで結果を変えたい、というのがやりたい事でした。フィルターを掛けた場合には可視セルの中で重複しない値を何とかしてカウントできないものかとと、重複の削除機能は使用しておらず、関数のみで算出が可能なのではないかと思いまして質問させて頂いた次第です。
何度も手を煩わせてしまいすみません、
教えて頂いた関数ではOFFSETも配列に使用できるのだと勉強になりました。ありがとうございます。

回答No.1

noname#203218

subtotal関数を用いると可視セル集計出来ます。

A列の可視セルのみカウントしています。
２がcount関数をしています。
数値指定で利用出来る関数が異なります、詳細は下記リンクサイト参照
=SUBTOTAL(2,A:A)

http://exinfo.biz/func/func_subtotal.shtml

補足

早速ご回答をくださりありがとうございます。
教えて頂いたSUBTOTAL関数ですと、オートフィルタで抽出していない状態での重複を除いた結果が出ないので、フィルタをかけていない時は重複を除いた結果を表示、抽出条件をつけてフィルタをかけた場合には可視セルを計算、としたいのですが・・・可能でしょうか？