Counting unique text entries in a filtered list...
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 ,
SOS 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
|