farid2001
4/11/2009 3:50:00 AM
Thanks a lot, it worked perfectly!!
Regards
farid2001
"Sheeloo" wrote:
> You can achieve that by
>
> =COUNTIFS(A2:A14,">=1152144",A2:A14,"<=1152555",B2:B14,"<=0.75")
> or
> =SUMPRODUCT(--(A2:A14 >= 1152144),--(A2:A14 <=1152555),--(B2:B14 <=0.75))
>
> -------------------------------------
> Pl. click ''Yes'' if this was helpful...
>
>
>
> "farid2001" wrote:
>
> > Hello Chip and Sheeloo
> >
> > This is what I am trying to accomplish:
> >
> > I have 2 columns, Column A has country codes, Column B has duration in
> > minutes.
> >
> > ccodes durt
> > 1151199 0.5
> > 1151442 2.25
> > 1152144 1.25
> > 1152199 0.25
> > 1154224 1
> > 1152222 0.75
> > 1152443 0.75
> > 1156727 1.25
> > 1157161 1.75
> > 1152477 1
> > 1152444 0.25
> > 1152555 1.25
> > 1158212 1.5
> >
> > I want to find how many entries had a duration of <= 0.75 on the codes that
> > are between the values of 1152144 and 1152555
> >
> > Thanks & regards
> > farid2001
> >
> >
> > "Chip Pearson" wrote:
> >
> > >
> > > Your code is rather confusing. Why are you doing an AND operation
> > > between the difference of your CountIfs and another CountIfs?
> > >
> > > Perhaps if you described what you are trying to accomplish, someone
> > > will post the appropriate code.
> > >
> > > If you want a simple CountBetween function, use something like
> > >
> > > Function CountBetween(Arr As Range, LowVal As Double, HighVal As
> > > Double) As Variant
> > > Dim L As Long
> > > If LowVal > HighVal Then
> > > CountBetween = CVErr(xlErrNum)
> > > Exit Function
> > > End If
> > > If Arr Is Nothing Then
> > > CountBetween = CVErr(xlErrRef)
> > > Exit Function
> > > End If
> > > With Application.WorksheetFunction
> > > L = .CountIf(Arr, "<=" & HighVal) - .CountIf(Arr, "<=" &
> > > LowVal)
> > > End With
> > > CountBetween = L
> > > End Function
> > >
> > > Adjust the "<=" comparison operators to "<" depending on whether you
> > > want an inclusive or exclusive between. That is, if LowVal is 5, is 5
> > > between LowVal and whatever HighVal might be?
> > >
> > > You can then call this from a cell with
> > >
> > > =CountBetween(A1:A10,5,8)
> > >
> > > Cordially,
> > > Chip Pearson
> > > Microsoft Most Valuable Professional
> > > Excel Product Group, 1998 - 2009
> > > Pearson Software Consulting, LLC
> > > www.cpearson.com
> > > (email on web site)
> > >
> > >
> > >
> > >
> > > On Fri, 10 Apr 2009 12:45:01 -0700, farid2001
> > > <farid2001@discussions.microsoft.com> wrote:
> > >
> > > >Dear Gentlemen
> > > >
> > > >I am trying to get the CountBetween function to work with CountIfs instead
> > > >of CountIf, but I am getting nowhere.
> > > >
> > > >This is what I have:
> > > >
> > > >Function CountBetween1(InRange, num1, num2, InRange1, num3) As Integer
> > > >
> > > > With Application.WorksheetFunction
> > > > If num1 <= num2 Then
> > > > CountBetween1 = .CountIfs(InRange, ">=" & num1) -
> > > >.CountIfs(InRange, ">" & num2) And .CountIfs(InRange1, ">=" & num3)
> > > > Else
> > > > CountBetween1 = .CountIfs(InRange, ">=" & num2) -
> > > >.CountIfs(InRange, ">" & num1) And .CountIfs(InRange1, ">=" & num3)
> > > > End If
> > > > End With
> > > >End Function
> > > >
> > > >Your help will be greatly appreciated.
> > > >
> > > >Regards
> > > >farid2001
> > >