Counting unique, filtered entries

  • Thread starter Thread starter Matt Lunn
  • Start date Start date
M

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
 
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
 
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
 
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.
 
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
 
Back
Top