Thanks for the reply, Peo. I tried using the following function (the one you
pointed to with my range substituted):
=SUM(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(ROW(A2:A100)),,1)),(MMULT(((A2:A100=TRANSPOSE( A2:A100)*TRANSPOSE(SUBTOTAL(3,OFFSET(A2,ROW(A2:A10 0)-MIN(ROW(A2:A100)),,1))))*(ROW(A2:A100)=TRANSPOSE( ROW(A2:A100)))),ROW(A2:A100)*0+1)
=1)*1))
It looks like that requires numeric entries (MMULT function)? I get a #VALUE
error. I have text entries in the cells I am trying to count.
Any other ideas, anyone, or can you help me understand what I did wrong
translating the idea provided by Peo?
-- Ian
"Peo Sjoblom" wrote:
One way albeit rather complicated
http://tinyurl.com/9rfmv
Regards,
Peo Sjoblom
"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?