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