Counting unique, filtered entries

[Matt Lunn]

Okay - heres a challenge for you excel gurus....

How can you count the unique entries on a filtered list.
I can extract unique entries using the following array
formula

=SUM(1/COUNTIF(A1:A10,A1:A10))

but when you use the filter the array formula obviously
just refers to these cells. How could I count only the
unique entries in the visble range.

Cheers,
Matt

 

[Peo Sjoblom]

You could use a help column and countif, like

=COUNTIF($A$2:A2,A2)=1

copied down it will return true/false

now when you have done the filter, finally filter on true in the help column
then use

=SUBTOTAL3,Range)

to count ranges

 

[Guest]

Hi, thanks for taking the time to reply.

Unfortunately I can't seem to get that to work.

I'd like this to work in the same way as SUBTOTAL
acheives it's summing. When the filter is changed
SUBTOTAL,9 sums only the visible cells. I'd like a cell
next to this which would count only the unique entries
which the filter produces..

Thanks,
Matt

 

[Peo Sjoblom]

It is very complicated to do that, my workaround will work if you insert a
help column
adapt the formula I gave to your data and finally after filtering the way
you have done until now, filter on the help column selecting TRUE.

 

[Guest]

Hi Peo,

I'm afraid I just cannot work out what to do here, could
you please explain what to do in a little more detail?
I've ever heard of a help column..

Thanks,
Matt