Try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9:A1000,RO W(A9:A1000)-MIN(ROW(A9:
A1000)),0,1)),MATCH(A9:A1000,A9:A1000,0)),IF(SUBTO TAL(3,OFFSET(A9:A1000,R
OW(A9:A1000)-MIN(ROW(A9:A1000)),0,1)),MATCH(A9:A1000,A9:A1000,0 )))0,1,0)
)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Ian wrote:

Hello.

I need to count unique values in a list. I have many ways to do this (I am
currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0,
1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine.

But - I wish to be able to filter the list and have the function adjust to
only the visible cells.

I tried substituting the SUBTOTAL function for SUM in the formula above, but
I get an ERROR result.

I am willing to use intermediate columns if I must. Can anyone help?