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.

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

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.

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.

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

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.

.

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.

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