Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I wrap text in a drop down list? | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Text to Columns from drop down list update | Excel Discussion (Misc queries) | |||
filter list of text for unique entries using formulea | Excel Worksheet Functions | |||
Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) |