| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
How do I count the # of unique occurences of a text in a column?
I am looking for a command/formulat to count (return a single number) the
number of unique text entries in a column. |
|
#2
|
|||
|
|||
|
One way
=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Regards, Peo Sjoblom "Rob Kaiser" <Rob wrote in message ... I am looking for a command/formulat to count (return a single number) the number of unique text entries in a column. |
|
#3
|
|||
|
|||
|
This is VERY resource intensive, so be careful using it on a list of several
thousand entries It's an array formula - enter it with the key combination Ctrl+Shift+Enter =SUM(1/COUNTIF(A1:A10,A1:A10)) "Rob Kaiser" wrote: I am looking for a command/formulat to count (return a single number) the number of unique text entries in a column. |
|
#4
|
|||
|
|||
|
I like yours MUCH better, Peo
"Peo Sjoblom" wrote: One way =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Regards, Peo Sjoblom "Rob Kaiser" <Rob wrote in message ... I am looking for a command/formulat to count (return a single number) the number of unique text entries in a column. |
|
#5
|
|||
|
|||
|
It's the SumProduct version of Hager's formula extended to take care of
formula-blanks and empty cells, {=SUM(IF(A1:A10<"",1/COUNTIF(A1:A10,A1:A10)))} Duke Carey wrote: I like yours MUCH better, Peo "Peo Sjoblom" wrote: One way =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Regards, Peo Sjoblom "Rob Kaiser" <Rob wrote in message ... I am looking for a command/formulat to count (return a single number) the number of unique text entries in a column. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the # of unique occurences of a text in a colum
Experts
How do you adjust this to account for columns that may be different in length? One time you need A375 and the next time you use it is A584. JayMan "Aladin Akyurek" wrote: It's the SumProduct version of Hager's formula extended to take care of formula-blanks and empty cells, {=SUM(IF(A1:A10<"",1/COUNTIF(A1:A10,A1:A10)))} Duke Carey wrote: I like yours MUCH better, Peo "Peo Sjoblom" wrote: One way =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Regards, Peo Sjoblom "Rob Kaiser" <Rob wrote in message ... I am looking for a command/formulat to count (return a single number) the number of unique text entries in a column. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the # of unique occurences of a text in a colum
Sounds like you want to use a dynamic range. See this:
http://contextures.com/xlNames01.html#Dynamic -- Biff Microsoft Excel MVP "JayMan" wrote in message ... Experts How do you adjust this to account for columns that may be different in length? One time you need A375 and the next time you use it is A584. JayMan "Aladin Akyurek" wrote: It's the SumProduct version of Hager's formula extended to take care of formula-blanks and empty cells, {=SUM(IF(A1:A10<"",1/COUNTIF(A1:A10,A1:A10)))} Duke Carey wrote: I like yours MUCH better, Peo "Peo Sjoblom" wrote: One way =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Regards, Peo Sjoblom "Rob Kaiser" <Rob wrote in message ... I am looking for a command/formulat to count (return a single number) the number of unique text entries in a column. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the # of unique occurences of a text in a colum
T
Do I now plug that in like this, Range is the name of the range {=SUM(IF(Range<"",1/COUNTIF(Range,Range)))} not really sure here JayMan "T. Valko" wrote: Sounds like you want to use a dynamic range. See this: http://contextures.com/xlNames01.html#Dynamic -- Biff Microsoft Excel MVP "JayMan" wrote in message ... Experts How do you adjust this to account for columns that may be different in length? One time you need A375 and the next time you use it is A584. JayMan "Aladin Akyurek" wrote: It's the SumProduct version of Hager's formula extended to take care of formula-blanks and empty cells, {=SUM(IF(A1:A10<"",1/COUNTIF(A1:A10,A1:A10)))} Duke Carey wrote: I like yours MUCH better, Peo "Peo Sjoblom" wrote: One way =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Regards, Peo Sjoblom "Rob Kaiser" <Rob wrote in message ... I am looking for a command/formulat to count (return a single number) the number of unique text entries in a column. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the # of unique occurences of a text in a colum
Yes. However, I would use the SUMPRODUCT formula that was posted in your
thread: =SUMPRODUCT((Range<"")/COUNTIF(Range,Range&"")) -- Biff Microsoft Excel MVP "JayMan" wrote in message ... T Do I now plug that in like this, Range is the name of the range {=SUM(IF(Range<"",1/COUNTIF(Range,Range)))} not really sure here JayMan "T. Valko" wrote: Sounds like you want to use a dynamic range. See this: http://contextures.com/xlNames01.html#Dynamic -- Biff Microsoft Excel MVP "JayMan" wrote in message ... Experts How do you adjust this to account for columns that may be different in length? One time you need A375 and the next time you use it is A584. JayMan "Aladin Akyurek" wrote: It's the SumProduct version of Hager's formula extended to take care of formula-blanks and empty cells, {=SUM(IF(A1:A10<"",1/COUNTIF(A1:A10,A1:A10)))} Duke Carey wrote: I like yours MUCH better, Peo "Peo Sjoblom" wrote: One way =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Regards, Peo Sjoblom "Rob Kaiser" <Rob wrote in message ... I am looking for a command/formulat to count (return a single number) the number of unique text entries in a column. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the # of unique occurences of a text in a colum
THANK YOU
"T. Valko" wrote: Yes. However, I would use the SUMPRODUCT formula that was posted in your thread: =SUMPRODUCT((Range<"")/COUNTIF(Range,Range&"")) -- Biff Microsoft Excel MVP "JayMan" wrote in message ... T Do I now plug that in like this, Range is the name of the range {=SUM(IF(Range<"",1/COUNTIF(Range,Range)))} not really sure here JayMan "T. Valko" wrote: Sounds like you want to use a dynamic range. See this: http://contextures.com/xlNames01.html#Dynamic -- Biff Microsoft Excel MVP "JayMan" wrote in message ... Experts How do you adjust this to account for columns that may be different in length? One time you need A375 and the next time you use it is A584. JayMan "Aladin Akyurek" wrote: It's the SumProduct version of Hager's formula extended to take care of formula-blanks and empty cells, {=SUM(IF(A1:A10<"",1/COUNTIF(A1:A10,A1:A10)))} Duke Carey wrote: I like yours MUCH better, Peo "Peo Sjoblom" wrote: One way =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Regards, Peo Sjoblom "Rob Kaiser" <Rob wrote in message ... I am looking for a command/formulat to count (return a single number) the number of unique text entries in a column. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the # of unique occurences of a text in a colum
You're welcome!
-- Biff Microsoft Excel MVP "JayMan" wrote in message ... THANK YOU "T. Valko" wrote: Yes. However, I would use the SUMPRODUCT formula that was posted in your thread: =SUMPRODUCT((Range<"")/COUNTIF(Range,Range&"")) -- Biff Microsoft Excel MVP "JayMan" wrote in message ... T Do I now plug that in like this, Range is the name of the range {=SUM(IF(Range<"",1/COUNTIF(Range,Range)))} not really sure here JayMan "T. Valko" wrote: Sounds like you want to use a dynamic range. See this: http://contextures.com/xlNames01.html#Dynamic -- Biff Microsoft Excel MVP "JayMan" wrote in message ... Experts How do you adjust this to account for columns that may be different in length? One time you need A375 and the next time you use it is A584. JayMan "Aladin Akyurek" wrote: It's the SumProduct version of Hager's formula extended to take care of formula-blanks and empty cells, {=SUM(IF(A1:A10<"",1/COUNTIF(A1:A10,A1:A10)))} Duke Carey wrote: I like yours MUCH better, Peo "Peo Sjoblom" wrote: One way =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Regards, Peo Sjoblom "Rob Kaiser" <Rob wrote in message ... I am looking for a command/formulat to count (return a single number) the number of unique text entries in a column. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Advanced unique cell count with multiple conditions ... help! | Excel Worksheet Functions | |||
| count unique with conditions | Excel Worksheet Functions | |||
| List Unique Occurences and Count | Excel Worksheet Functions | |||
| Count Unique Values | Excel Worksheet Functions | |||
| Pivot Table Unique Count | Excel Worksheet Functions | |||
Linear Mode
