Count filtered text only

[scoobyblue]

I have a spreadsheet with a list of headings down column 'B'. Following formula calculates the number of unique text values; =SUMPRODUCT(1/COUNTIF(B6:B192,B6:B192))

This produces a result of '10', but when it is filtered and showing less visible unique text values it still shows '10'. How can my formula adjust to show number of visible unique text values after applying a filter?

Also, if the range in the formula can be extended to accommodate future additions to the column this would be useful. At present the range has to end at the last entry 'B192', otherwise the result is ♯DIV/0!. So when additions to column are input I have to keep extending the range in the formula.

Many thanks for your help.
Scoobyblue

 

[VoG]

Duplicate of http://www.mrexcel.com/forum/showthread.php?t=576583

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).