Find Unique
 

OK, I have a table like so:

Name Score
------ -------
Bob 80
Jane 90
Jeff 80
Jack 90
Bob 70
Bob 60

I would like to create a formula that will tell me how many unique names are
in column A, so the result in the above case would be 4. How do I do that?

Find Unique
 

Try this:

=SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&""))

Biff

"Sean Timmons" wrote in message
...
OK, I have a table like so:

Name Score
------ -------
Bob 80
Jane 90
Jeff 80
Jack 90
Bob 70
Bob 60

I would like to create a formula that will tell me how many unique names
are
in column A, so the result in the above case would be 4. How do I do that?

Find Unique
 

Perfect! That was killing me! Thank you so much!

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&""))

Biff

"Sean Timmons" wrote in message
...
OK, I have a table like so:

Name Score
------ -------
Bob 80
Jane 90
Jeff 80
Jack 90
Bob 70
Bob 60

I would like to create a formula that will tell me how many unique names
are
in column A, so the result in the above case would be 4. How do I do that?

Find Unique
 

You're welcome. Thanks for the feedback!

Biff

"Sean Timmons" wrote in message
...
Perfect! That was killing me! Thank you so much!

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&""))

Biff

"Sean Timmons" wrote in message
...
OK, I have a table like so:

Name Score
------ -------
Bob 80
Jane 90
Jeff 80
Jack 90
Bob 70
Bob 60

I would like to create a formula that will tell me how many unique
names
are
in column A, so the result in the above case would be 4. How do I do
that?

Find Unique
 

damn Biff i c that 1 works great, but plase just please dont ask me why :-)

great job



"T. Valko" skrev:

You're welcome. Thanks for the feedback!

Biff

"Sean Timmons" wrote in message
...
Perfect! That was killing me! Thank you so much!

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&""))

Biff

"Sean Timmons" wrote in message
...
OK, I have a table like so:

Name Score
------ -------
Bob 80
Jane 90
Jeff 80
Jack 90
Bob 70
Bob 60

I would like to create a formula that will tell me how many unique
names
are
in column A, so the result in the above case would be 4. How do I do
that?

Find Unique
 

To see how it works break it down into steps:

A1:A6 = Bob, Jane, Jeff, Jack, Bob, Bob

Enter this formula in B1 and copy down to B6:

=A1<""

Enter this formula in C1 and copy down to C6:

=COUNTIF(A$1:A$6,A1&"")

Enter this formula in D1 and copy down to D6:

=B1/C1

Then: =SUM(D1:D6)

Now, to see why we use this expression: A1:A6&""

Change the formula in C1 to: =COUNTIF(A$1:A$6,A1) and copy down to C6.

Now, clear the contents of cell A5 and see what happens.

If you know for certain that there will never be empty/blank cells in the
range then this will suffice:

=SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6))

Biff

"excelent" wrote in message
...
damn Biff i c that 1 works great, but plase just please dont ask me why
:-)

great job



"T. Valko" skrev:

You're welcome. Thanks for the feedback!

Biff

"Sean Timmons" wrote in message
...
Perfect! That was killing me! Thank you so much!

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&""))

Biff

"Sean Timmons" wrote in
message
...
OK, I have a table like so:

Name Score
------ -------
Bob 80
Jane 90
Jeff 80
Jack 90
Bob 70
Bob 60

I would like to create a formula that will tell me how many unique
names
are
in column A, so the result in the above case would be 4. How do I do
that?

Find Unique
 

well ok it help to figure out that one pheeew :-)



"T. Valko" skrev:

To see how it works break it down into steps:

A1:A6 = Bob, Jane, Jeff, Jack, Bob, Bob

Enter this formula in B1 and copy down to B6:

=A1<""

Enter this formula in C1 and copy down to C6:

=COUNTIF(A$1:A$6,A1&"")

Enter this formula in D1 and copy down to D6:

=B1/C1

Then: =SUM(D1:D6)

Now, to see why we use this expression: A1:A6&""

Change the formula in C1 to: =COUNTIF(A$1:A$6,A1) and copy down to C6.

Now, clear the contents of cell A5 and see what happens.

If you know for certain that there will never be empty/blank cells in the
range then this will suffice:

=SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6))

Biff

"excelent" wrote in message
...
damn Biff i c that 1 works great, but plase just please dont ask me why
:-)

great job



"T. Valko" skrev:

You're welcome. Thanks for the feedback!

Biff

"Sean Timmons" wrote in message
...
Perfect! That was killing me! Thank you so much!

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A6<"")/COUNTIF(A1:A6,A1:A6&""))

Biff

"Sean Timmons" wrote in
message
...
OK, I have a table like so:

Name Score
------ -------
Bob 80
Jane 90
Jeff 80
Jack 90
Bob 70
Bob 60

I would like to create a formula that will tell me how many unique
names
are
in column A, so the result in the above case would be 4. How do I do
that?