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 -- SOS ------------------------------------------------------------------------ SOS's Profile: http://www.excelforum.com/member.php...fo&userid=5406 View this thread: http://www.excelforum.com/showthread...hreadid=530034 |
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 |
Counting unique text entries in a filtered list...
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: http://www.excelforum.com/member.php...fo&userid=5406 View this thread: http://www.excelforum.com/showthread...hreadid=530034 |
Counting unique text entries in a filtered list...
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 , SOS 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 |
Counting unique text entries in a filtered list...
Domenic, I typed the fomula in and it works perfectly. Many thanks for your input Seamus -- SOS ------------------------------------------------------------------------ SOS's Profile: http://www.excelforum.com/member.php...fo&userid=5406 View this thread: http://www.excelforum.com/showthread...hreadid=530034 |
All times are GMT +1. The time now is 07:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com