Count Different Values in a column

's Computer Specifications

I need to know the number of differing entries in a column.
For Example

Name:
George
Carol
Don
George
Don
Carol
Pat
Sam
George

Should return 5 because there are 5 different names.

's Computer Specifications

=SUMPRODUCT((A2:A200<>"")/COUNTIF(A2:A200,A2:A200&""))

--
__________________________________
HTH

Bob

"KJ MAN" <(E-Mail Removed)> wrote in message
news:97073118-85FD-4C0E-94BA-(E-Mail Removed)...
>I need to know the number of differing entries in a column.
> For Example
>
> Name:
> George
> Carol
> Don
> George
> Don
> Carol
> Pat
> Sam
> George
>
> Should return 5 because there are 5 different names.

's Computer Specifications

If the values are in the range A1:A10 then

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

The first part gives 1 for each cell that is not empty. Then divides it
with how many instances there is of this value.
Summing that gives you the "unique count".

The &"" is to avoid 0 and #DIV/0 when a cell is empty.

Ref: http://www.mrexcel.com/archive2/63500/73502.htm

Happy Programming!
- Suranthe

*** Sent via Developersdex http://www.developersdex.com ***

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Count Unique Values in 1 Column based on Date Range in another Column	Brian	Microsoft Excel Worksheet Functions	28	17th May 2009 02:58 PM
Count Unique Values in 1 Column based on Date Range in another Column	Brian	Microsoft Excel Worksheet Functions	0	16th May 2009 04:44 PM
Count unique values in one column if values in corresponding columnare null?	allie357	Microsoft Excel Programming	1	7th Dec 2008 09:37 AM
Count Intervals of Filtered TEXT values in Column and Return Count across a Row	Sam via OfficeKB.com	Microsoft Excel Worksheet Functions	9	31st Jul 2005 03:37 AM
Count of values in a column based on values in another column	student	Microsoft Excel Discussion	9	19th Aug 2004 02:30 PM