Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting the # of unique values
I have a file with multiple worksheets. I need to find the # of unique values
in D:D. The values in D:D are alpha numeric Ex. Values 1a 1d 2e 1a That should return the # "3" in the destination cell. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting the # of unique values
You can't use entire columns as references with either of these formulas:
(in versions of Excel prior to Excel 2007) If there will never be any empty/blank cells within the range: =SUMPRODUCT(1/COUNTIF(D1:D4,D1:D4)) If there might be empty/blank cells within the range: =SUMPRODUCT((D1:D4<"")/COUNTIF(D1:D4,D1:D4&"")) Biff "bobby769" wrote in message ... I have a file with multiple worksheets. I need to find the # of unique values in D:D. The values in D:D are alpha numeric Ex. Values 1a 1d 2e 1a That should return the # "3" in the destination cell. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting the # of unique values
=SUM(IF(FREQUENCY(MATCH(A1:A4,A1:A4,0),MATCH(A1:A4 ,A1:A4,0))0,1))
ctrl+shift+enter, not just enter "bobby769" wrote: I have a file with multiple worksheets. I need to find the # of unique values in D:D. The values in D:D are alpha numeric Ex. Values 1a 1d 2e 1a That should return the # "3" in the destination cell. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting the # of unique values
Which formula did you want to use?
Try this: =SUMPRODUCT((D1:D65535<"")/COUNTIF(D1:D65535,D1:D65535&""))+(D65536<"")*(ISN A(MATCH(D65536,D1:D65535,0)) I hope you have a "strong" machine! That could take a while to calculate! Biff "bobby769" wrote in message ... So what do I do if the entire column has data? Yes, its a very large file. Is my only option to upgrade to Excel '07? "T. Valko" wrote: You can't use entire columns as references with either of these formulas: (in versions of Excel prior to Excel 2007) If there will never be any empty/blank cells within the range: =SUMPRODUCT(1/COUNTIF(D1:D4,D1:D4)) If there might be empty/blank cells within the range: =SUMPRODUCT((D1:D4<"")/COUNTIF(D1:D4,D1:D4&"")) Biff "bobby769" wrote in message ... I have a file with multiple worksheets. I need to find the # of unique values in D:D. The values in D:D are alpha numeric Ex. Values 1a 1d 2e 1a That should return the # "3" in the destination cell. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique values with criteria | Excel Worksheet Functions | |||
list unique values in a column | Excel Worksheet Functions | |||
Populate combo box with unique values only | Excel Discussion (Misc queries) | |||
Counting Values | Excel Worksheet Functions | |||
Counting unique values + frequency | Excel Worksheet Functions |