Count Unique Values in a Filtered Column

I need to count the number of unique names from a column of filtered text. On the attached example D5 is where I need the value, the current array I have does not work when the filter is applied by changing the product in drop box. I need to have a count of the unique account names from column A. Thanks.

I've used some user defined functions on the attachment to give a count of the visible items. I've used the range.Hidden property to help. I also had to get the combo box change event to reapply the formulae in order to get the UDF's to run.

Hope this helps.

I can't open your file I'm afraid as I only have xl2002 at work, but assuming your filter range is from A2:A100 (headers in A1) then you could use:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),MATCH(A2:A100,A2:A100,0)),MATCH(A2:A100,A2:A100,0)),1))

which is an array formula and so must be confirmed with Ctrl+Shift+Enter (following successful entry, Excel will surround with curly braces {} - do not try entering these manually yourself!).

Richard