Counting unique text entries in a filtered list...

Hi all,

I'd like to be able to count unique text entries in Column A and have messed aroubd with the following formula:

=SUM(IF(FREQUENCY(IF(LEN(A2:A100)>0,MATCH(A2:A100,A2:A100,0),""),
IF(LEN(A2:A100)>0,MATCH(A2:A100,A2:A100,0),""))>0,1))

entered as an array formula and it works.

However I'd like to be able to apply that formula after I have filtered the worksheet on another column.

Example:

Name Gender
John Male
John Male
Mary Female
Margaret Female

The above formula gives me the answer 3 (correctly). But when I filter the sheet on "Gender" as Male I'd like the answer to show 1.

Does anyone know if this is possible?

Many thanks

Seamus

Try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1))
,MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1)>0,1))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <[email protected]>,
SOS <[email protected]> wrote:

> Hi all,
>
> I'd like to be able to count unique text entries in Column A and have
> messed aroubd with the following formula:
>
> =SUM(IF(FREQUENCY(IF(LEN(A2:A100)>0,MATCH(A2:A100,A2:A100,0),""),
> IF(LEN(A2:A100)>0,MATCH(A2:A100,A2:A100,0),""))>0,1))
>
> entered as an array formula and it works.
>
> However I'd like to be able to apply that formula after I have filtered
> the worksheet on another column.
>
> Example:
>
> Name Gender
> John Male
> John Male
> Mary Female
> Margaret Female
>
> The above formula gives me the answer 3 (correctly). But when I filter
> the sheet on "Gender" as Male I'd like the answer to show 1.
>
> Does anyone know if this is possible?
>
> Many thanks
>
> Seamus

Domenic,

Thanks for the reply. I pasted your example into my workbook as an array formula (CTR+SHIFT+ENTER) but ended up with #NAME? Error in the cell.

Any ideas?

Seamus

Since you copied/pasted the formula into your workbook, it's possible
that a hard return may have been added. Try typing out the formula
instead. Does that help?

In article <[email protected]>,
SOS <[email protected]> wrote:

> Domenic,
>
> Thanks for the reply. I pasted your example into my workbook as an
> array formula (CTR+SHIFT+ENTER) but ended up with #NAME? Error in the
> cell.
>
> Any ideas?
>
> Seamus

Domenic,

I typed the fomula in and it works perfectly. Many thanks for your input

Seamus