count filtered unique text values

scoobyblue said:

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

Click to expand...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B6,ROW(B6:B192)-ROW(B6),,1)),
    IF(B6:B192<>"",MATCH("~"&B6:B192,B6:B192&"",0))),
     ROW(B6:B192)-ROW(B6)+1),1))

scoobyblue said:

Result I get is ♯VALUE!

Click to expand...

scoobyblue said:

Thanks - was in a rush and didn't spot your text above saying about 'ctrl+shift+enter'. Many Thanks works perfectly.

Click to expand...