Count Number of different numbers
 

Good day, i would like to know if it is possible to count the numbmer of
different numbers in a specific column.e.g.
A
A05282
A05282
A05282
A05282
A05282
A05282
A05286
A05286
A05286
A05286
A05310
A05310

The count should give me 3 as there are 3 different numbers in the
column.



Les Stout

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

Count Number of different numbers
 

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Les Stout" wrote in message
...
Good day, i would like to know if it is possible to count the numbmer of
different numbers in a specific column.e.g.
A
A05282
A05282
A05282
A05282
A05282
A05282
A05286
A05286
A05286
A05286
A05310
A05310

The count should give me 3 as there are 3 different numbers in the
column.



Les Stout

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

Count Number of different numbers
 

Thanks so much Bob, could you please help me with this. I need to do
this with code and insert the result in a cell in a new workbook with
the date next to it.

Les Stout

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

Count Number of different numbers
 

I would use the same approach Les, just put it in code.

Here is an example, with all the relevant data setup in constants to enable
easy res-etting

'constants to define data source
Const WB_NAME = "BS Accounts.xls"
Const WS_NAME = "Lynne"
Const WS_RANGE = "A1:A20"
Dim cUnique As Long
Dim sFormula As String
Dim sRange As String

sRange =
Workbooks(WB_NAME).Worksheets(WS_NAME).Range(WS_RA NGE).Address(, , , True)
sFormula = "SUMPRODUCT((" & sRange & _
"<"""")/COUNTIF(" & sRange & "," & sRange & "&""""))"

cUnique = ActiveSheet.Evaluate(sFormula)
Workbooks("newbook.xls").Worksheets("Sheet1").Rang e("A1").Value =
cUnique


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Les Stout" wrote in message
...
Thanks so much Bob, could you please help me with this. I need to do
this with code and insert the result in a cell in a new workbook with
the date next to it.

Les Stout

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

Count Number of different numbers
 

Thanks a mill Bob, have agreat evening.

best regards,

Les Stout

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