Countif
Hi Patrick
If my source is Sheets(3).Range("NOrderID") then
MsgBox uniquecount(Sheets(3).Range("NOrderID")) returns 1
whereas the answer should be 5
I am missing something and would be grateful for any further assistance.
Geoff
"Patrick Molloy" wrote:
try this:
Function uniquecount(source As Range) As Long
Dim index As Long
Dim count As Long
Dim cell As Long
If source.count 0 Then count = 1
For index = 1 To source.count - 1
If Int(source(index)) < Int(source(index + 1)) Then
count = count + 1
End If
Next
uniquecount = count
End Function
"Geoff" wrote in message
...
Hi
I need to count unique items in a list of indices for example:
13.1
13.2
13.3
14.1
15.1
16.1
16.2
The answer would be 4 as the only count I need is the outer index.
The indices are compiled as strings and are in named ranges on several
sheets. I am looking for something like:
lastorderID = 12.1
For i = 1 to sheets .count-1
res(i) = Application.CountIf(Sheets(i).Range("NOrderID"), "<
CInt(lastorderID)")
Next
or:
res(i) = Application.CountIf(Sheets(i).Range("NOrderID"), "<
Left((lastorderID),2)")
But they do not work. Can someone please help?
T.I.A.
|