Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i find unique avg buy prices in multiple group of buys/sell | Excel Worksheet Functions | |||
Trapping a NO FIND after a find | Excel Discussion (Misc queries) | |||
Find last occurance of text in range | Excel Worksheet Functions | |||
Find within Workbook. | Excel Discussion (Misc queries) | |||
Find duplicates | Excel Discussion (Misc queries) |