Unique count of text

[Debbie]

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.

 

[Jason Morin]

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

 

[Domenic]

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!

 

[Debbie]

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??

 

[Harlan Grove]

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))

 

[Jason Morin]

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??

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!

 

[Ola]

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.

 

[Ola]

...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.