Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
I have a range of cells (A5:A100). In some cells there are duplicate part
numbers. I want to count ONLY the unique values and not the duplicates. Does anyone know how this can be done and what formula I would use to accomplish this? Thank you very much in advance, Karen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
One way:
=SUMPRODUCT((A5:A100<"")/COUNTIF(A5:A100,A5:A100&"")) If there are no empty cells within the range then you can reduce that to: =SUMPRODUCT(1/COUNTIF(A5:A100,A5:A100)) -- Biff Microsoft Excel MVP "Karen" wrote in message ... I have a range of cells (A5:A100). In some cells there are duplicate part numbers. I want to count ONLY the unique values and not the duplicates. Does anyone know how this can be done and what formula I would use to accomplish this? Thank you very much in advance, Karen |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Hello,
There is a bunch of possible solutions: http://sulprobil.com/html/count_unique.html Regards, Bernd |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
This will check the whole column, but you can restrict it to just the range
you've noted if you want. =SUM(IF(FREQUENCY(A:A,A:A)0,1)) -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Karen" wrote: I have a range of cells (A5:A100). In some cells there are duplicate part numbers. I want to count ONLY the unique values and not the duplicates. Does anyone know how this can be done and what formula I would use to accomplish this? Thank you very much in advance, Karen |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Thanks BerndP. That's pretty neat. I've seen other ways too. This list is
pretty comprehensive, I think: < -- =SUMPRODUCT((A2:A105<"")/(COUNTIF(A2:A105,A2:A105&""))) < -- =SUMPRODUCT((A2:A998<"")/(COUNTIF(A2:A998,A2:A998)+(A2:A998=""))) < -- =SUM(IF(FREQUENCY(IF(LEN(A2:A999)0,MATCH(A2:A999, A2:A999,0),""),IF(LEN(A2:A999)0,MATCH(A2:A999,A2: A999,0),""))0,1)) Note: This is a CSE Function < -- =SUMPRODUCT((A2:A105<"")/COUNTIF(A2:A105,A2:A105&"")*(A2:A105<"")) < -- =SUMPRODUCT(--(A2:A999<""),1/COUNTIF(A2:A999,A2:A999&"")) < -- =SUM(--(FREQUENCY(IF(A2:A2705<"",MATCH(A2:A2705,A2:A2705 ,0)),ROW(INDIRECT("1:"&ROWS(A2:A2705))))0)) Note: This is a CSE Function < -- =COUNT(1/FREQUENCY(A1:A400,A1:A400)) < -- =SUM(1/COUNTIF(C13:C17,C13:C17)) Note: This is a CSE Function & it fails if there are any blanks in between < -- =SUM(IF(A2:A400<"",1/COUNTIF(A2:A400,A2:A400))) < -- =SUM(IF(FREQUENCY(IF(A2:A1001<"",MATCH("~"&A2:A10 01,A2:A1001&"",0)),ROW(A2:A1001)-ROW(A2)+1),1)) I don't believe this list is exhaustive. There are probably many other ways of doing this. Maybe some have yet to be discovered (by anyone). HTH, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "JBeaucaire" wrote: This will check the whole column, but you can restrict it to just the range you've noted if you want. =SUM(IF(FREQUENCY(A:A,A:A)0,1)) -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Karen" wrote: I have a range of cells (A5:A100). In some cells there are duplicate part numbers. I want to count ONLY the unique values and not the duplicates. Does anyone know how this can be done and what formula I would use to accomplish this? Thank you very much in advance, Karen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Values | Charts and Charting in Excel | |||
Counting unique values | Excel Worksheet Functions | |||
counting the # of unique values | Excel Worksheet Functions | |||
Counting unique values | Excel Worksheet Functions | |||
Counting Unique Values | Excel Worksheet Functions |