Count Number of different numbers

[Les Stout]

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 ***

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Les Stout" <[email protected]> wrote in message
news:%[email protected]...
> 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 ***

[Les Stout]

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 ***

[Bob Phillips]

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_RANGE).Address(, , , True)
sFormula = "SUMPRODUCT((" & sRange & _
"<>"""")/COUNTIF(" & sRange & "," & sRange & "&""""))"

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


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Les Stout" <[email protected]> wrote in message
news:%[email protected]...
> 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 ***

[Les Stout]

Thanks a mill Bob, have agreat evening.

best regards,

Les Stout

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