Countif

[Geoff]

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.

[Patrick Molloy]

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

[Geoff]

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

[Geoff]

Hi Patrick
Further to the last - more accurately the return was counting where there
was a blank. I then set If source.count > 0 Then count = 1 to
If source.count > 0 Then count = 0 and all was well. All I have to do now
is iterate through the sheets and job done.
Thank you.

Geoff

"Geoff" wrote:

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

[Harlan Grove]

"Geoff" <[email protected]> wrote...
>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:

This could be done in a worksheet cell formula using

=SUMPRODUCT(1/COUNTIF(SomeRange,INT(SomeRange)&"*"))

In VBA, I'd be tempted to cheat and use Evaluate.

[Harlan Grove]

"Harlan Grove" <[email protected]> wrote...
....
>This could be done in a worksheet cell formula using
>
>=SUMPRODUCT(1/COUNTIF(SomeRange,INT(SomeRange)&"*"))
....

Not general enough. Make that

=SUMPRODUCT(1/COUNTIF(SomeRange,INT(SomeRange)&".*"))