software

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)gt;0,MATCH(A2:A100, A2:A100,0),quot;quot;),
IF(LEN(A2:A100)gt;0,MATCH(A2:A100,A2:A100,0),quot;quot;))gt;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 quot;Genderquot; as Male I'd like the answer to show 1.

Does anyone know if this is possible?

Many thanks

Seamus--
SOS
------------------------------------------------------------------------
SOS's Profile: www.excelforum.com/member.php...foamp;userid=5406
View this thread: www.excelforum.com/showthread...hreadid=530034Try...

=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)gt;0,1))

....confirmed with CONTROL SHIFT ENTER.

Hope this helps!

In article gt;,
SOS gt; wrote:

gt; Hi all,
gt;
gt; I'd like to be able to count unique text entries in Column A and have
gt; messed aroubd with the following formula:
gt;
gt; =SUM(IF(FREQUENCY(IF(LEN(A2:A100)gt;0,MATCH(A2:A100, A2:A100,0),quot;quot;),
gt; IF(LEN(A2:A100)gt;0,MATCH(A2:A100,A2:A100,0),quot;quot;))gt;0, 1))
gt;
gt; entered as an array formula and it works.
gt;
gt; However I'd like to be able to apply that formula after I have filtered
gt; the worksheet on another column.
gt;
gt; Example:
gt;
gt; Name Gender
gt; John Male
gt; John Male
gt; Mary Female
gt; Margaret Female
gt;
gt; The above formula gives me the answer 3 (correctly). But when I filter
gt; the sheet on quot;Genderquot; as Male I'd like the answer to show 1.
gt;
gt; Does anyone know if this is possible?
gt;
gt; Many thanks
gt;
gt; 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--
SOS
------------------------------------------------------------------------
SOS's Profile: www.excelforum.com/member.php...foamp;userid=5406
View this thread: www.excelforum.com/showthread...hreadid=530034Since 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 gt;,
SOS gt; wrote:

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


Domenic,

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

Seamus--
SOS
------------------------------------------------------------------------
SOS's Profile: www.excelforum.com/member.php...foamp;userid=5406
View this thread: www.excelforum.com/showthread...hreadid=530034