Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting names
I have a two columns A with surname and B with first name, but entered
multiple times: eg: Smith, John Smith, John Smith, Arthur, Rabbit, Peter, Rabbit, Peter Rabbit, Peter What's the easiest way to total up the number of people there are? In this case 3. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting names
Try this:-
=SUM(IF(FREQUENCY(IF(LEN(A1:A10)0,MATCH(A1:A10,A1 :A10,0),""),IF(LEN(A1:A10)0,MATCH(A1:A10,A1:A10,0 ),""))0,1)) It;s an array so Ctrl+Shift+enter " wrote: I have a two columns A with surname and B with first name, but entered multiple times: eg: Smith, John Smith, John Smith, Arthur, Rabbit, Peter, Rabbit, Peter Rabbit, Peter What's the easiest way to total up the number of people there are? In this case 3. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting names
I missed they are in 2 columns so concatenate them into a single column first
in C1 =A1&B1 Apply the formula to the concatenated column. Mike " wrote: I have a two columns A with surname and B with first name, but entered multiple times: eg: Smith, John Smith, John Smith, Arthur, Rabbit, Peter, Rabbit, Peter Rabbit, Peter What's the easiest way to total up the number of people there are? In this case 3. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting names
one way:
InC1: =A1&B1 and copy down in D1: =SUM(1/COUNTIF($C$1:$C$6,$C$1:$C$6)) Enter with Ctrl+Shift+Enter HTH " wrote: I have a two columns A with surname and B with first name, but entered multiple times: eg: Smith, John Smith, John Smith, Arthur, Rabbit, Peter, Rabbit, Peter Rabbit, Peter What's the easiest way to total up the number of people there are? In this case 3. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting names
On 18 Jun, 11:16, Toppers wrote:
one way: InC1: =A1&B1 and copy down in D1: =SUM(1/COUNTIF($C$1:$C$6,$C$1:$C$6)) Enter with Ctrl+Shift+Enter HTH " wrote: I have a two columns A with surname and B with first name, but entered multiple times: eg: Smith, John Smith, John Smith, Arthur, Rabbit, Peter, Rabbit, Peter Rabbit, Peter What's the easiest way to total up the number of people there are? In this case 3. Thanks- Hide quoted text - - Show quoted text - Many thanks to both of you. both worked. Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting names only once in a list | Excel Worksheet Functions | |||
Counting Names in a List | Excel Discussion (Misc queries) | |||
Counting Different Names in A Column... Formula? | New Users to Excel | |||
Counting how many different names in a long list | Excel Discussion (Misc queries) | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) |