Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Unique count of text
Hi I have first and last names in 2 columns and I need to count the number of
unique people. It's already a busy worksheet and would rather not have to filter and move to another worksheet. Any suggestions? Thanks. |
#2
|
|||
|
|||
One way:
=SUMPRODUCT(--(MATCH(Fname&Lname,Fname&Lname,0)=ROW (INDIRECT("1:"&ROWS(Fname))))) where "Fname" is the range for first names and "Lname" is the range of last names. HTH Jason Atlanta, GA -----Original Message----- Hi I have first and last names in 2 columns and I need to count the number of unique people. It's already a busy worksheet and would rather not have to filter and move to another worksheet. Any suggestions? Thanks. . |
#3
|
|||
|
|||
Try the following...
=SUM(IF(FREQUENCY(MATCH(A1:A10&B1:B10,A1:A10&B1:B1 0,0),ROW(INDIRECT("1:"& ROWS(A1:A10))))0,1)) OR =COUNT(1/FREQUENCY(MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0),ROW (INDIRECT("1:" &ROWS(A1:A10))))) Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Debbie wrote: Hi I have first and last names in 2 columns and I need to count the number of unique people. It's already a busy worksheet and would rather not have to filter and move to another worksheet. Any suggestions? Thanks. |
#4
|
|||
|
|||
Flint Dianne
Jones Amber Jackson Dorraine Smith Laurel Smith Lesley Smith Lesley Smith Lesley Jones Amber Jones Amber If these are my names and I use any of the formulas suggested the result returned is 5 and it should be 6?? Any suggestions?? "Domenic" wrote: Try the following... =SUM(IF(FREQUENCY(MATCH(A1:A10&B1:B10,A1:A10&B1:B1 0,0),ROW(INDIRECT("1:"& ROWS(A1:A10))))0,1)) OR =COUNT(1/FREQUENCY(MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0),ROW (INDIRECT("1:" &ROWS(A1:A10))))) Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Debbie wrote: Hi I have first and last names in 2 columns and I need to count the number of unique people. It's already a busy worksheet and would rather not have to filter and move to another worksheet. Any suggestions? Thanks. |
#5
|
|||
|
|||
Debbie wrote...
Flint Dianne Jones Amber Jackson Dorraine Smith Laurel Smith Lesley Smith Lesley Smith Lesley Jones Amber Jones Amber If these are my names and I use any of the formulas suggested the result returned is 5 and it should be 6?? .... There are only 5 distinct names. Flint Dianne Jones Amber Jackson Dorraine Smith Laurel Smith Lesley If you mean the 2nd name, the 1st instance of Jones Amber, should be considered distinct from the 8th and 9th names, the 2nd and 3rd instances of Jones Amber, then it actually gets easier. If these names were in A1:B9, use the formula =1+SUMPRODUCT(--(A2:A9&B2:B9<A1:A8&B1:B8)) |
#6
|
|||
|
|||
No, there are 5 unique values. Check again. I know my
formula works - I didn't try Domenic's, but I'm sure they do. Jason -----Original Message----- Flint Dianne Jones Amber Jackson Dorraine Smith Laurel Smith Lesley Smith Lesley Smith Lesley Jones Amber Jones Amber If these are my names and I use any of the formulas suggested the result returned is 5 and it should be 6?? Any suggestions?? "Domenic" wrote: Try the following... =SUM(IF(FREQUENCY(MATCH (A1:A10&B1:B10,A1:A10&B1:B10,0),ROW(INDIRECT("1:"& ROWS(A1:A10))))0,1)) OR =COUNT(1/FREQUENCY(MATCH (A1:A10&B1:B10,A1:A10&B1:B10,0),ROW(INDIRECT("1:" &ROWS(A1:A10))))) Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article <89EA2BA5-9511-4E56-8D75- , Debbie wrote: Hi I have first and last names in 2 columns and I need to count the number of unique people. It's already a busy worksheet and would rather not have to filter and move to another worksheet. Any suggestions? Thanks. . |
#7
|
|||
|
|||
Distinct Count:
=SUM(1/COUNTIF(A1:A9&B1:B9,A1:A9&B1:B9)) Example: Flint Dianne Jones Amber Jackson Dorraine Smith Laurel Smith Lesley Smith Lesley Smith Lesley Jones Amber Jones Amber =5 Ola Sandstrom Note: Confirm the formula by holding down Ctrl and Shift, and then press Enter. Othervise the result will be 1. |
#8
|
|||
|
|||
...if it should be 6...
Group Count: =SUM(--(A1:A8&B1:B8<A2:A9&B2:B9))+1 =6 Ola Note: Confirm the formula by holding down Ctrl and Shift, and then press Enter. Otherwise the result will be 1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count the text in a column - Part II | Excel Discussion (Misc queries) | |||
want if cell containts text, then increase count by one | Excel Worksheet Functions | |||
How do I count or display unique data in a column? | Excel Worksheet Functions | |||
Count of unique items meeting condition | Excel Worksheet Functions | |||
how do I 'count' the number of cells with a text in red or black? | Excel Worksheet Functions |