Unique entries in a filtered list

[Guest]

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 have already posted this once, why don't you give an example how you
filter.?
Do you filter for one value in one column and want the unique entries in a
second column?
If that is so you can concatenate those 2 columns. Assume you want to filter
A2:A100 for one value,
let's say the string "ABC", now you have values in B2:B100 that you want to
have unique values
of. Add 2 help columns, concatenate the values in A and B as follows

=A2&B2

copy down to row 100 (assume you do this in column G). Now in another help
column put

=COUNTIF($G$2:G2,G2)=1

copy down

now filter on A and on the second help column TRUE

 

[Guest]

Hi Peo,

I posted it again in case anyone missed it the first
time. I got your formula to work and although it was
impressive it is not applicable for the way I wish the
spreadsheet to be used.

There is a great deal of data in the spreadsheet and
performance and storage are important factors so I'm not
too keen on adding to this by concatenating columns.

Also the spreadsheet is also going to be used by others
and filtering/unfiltering by true when analysing the data
would make the user interface too complicated for some
novice users. If there is no way of doing this via a
formula then I suppose it'll have to be programmed in VBA.

Thanks for your help.

Matt

 

[Peo Sjoblom]

Matt, try this formula adapted from Daniel Maher

=CEILING(SUM((IF(LEN(A3:A100)>0,1/COUNTIF(A3:A100,A3:A100)))*(SUBTOTAL(3,OFF
SET(B3,ROW(B3:B100)-MIN(ROW(B3:B100)),,1)))),1)

it has to be array entered with ctrl + shift & enter

it will count unique entries in A3:A100 when B3:B100 have been filtered

Upon request I can email you privately a sample workbook