Count Number of different numbers

  • Thread starter Thread starter Les Stout
  • Start date Start date
L

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
 
=SUMPRODUCT((A1:A12<>"")/COUNTIF(A1:A12,A1:A12&""))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
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
 
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)
 
Back
Top