CountIfs

[farid2001]

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

 

[Chip Pearson]

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)

 

[Sheeloo]

There is nothing wrong with your formula... I spend 30 mins to find that out...

You are probably calling it as
=CountBetween1(A1:A10, 10, B1:B10, 20, 5)
which I was doing... and I rewrote everything then realized that your
function signature is
CountBetween1(InRange, num1, num2, InRange1, num3)

see what I mean?
You should call as
=CountBetween1(A1:A10, 10, 20, B1:B10, 5)



Actually you should change the signature of the function...

I am assuming you are working with EXCEL 2007.

 

[farid2001]

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

 

[Sheeloo]

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

 

[farid2001]

Thanks a lot, it worked perfectly!!

Regards
farid2001