Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIfs
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIfs
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIfs
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. ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "farid2001" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIfs
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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIfs
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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIfs
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countifs | Excel Worksheet Functions | |||
countifs/or | Excel Worksheet Functions | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
Countifs | Excel Worksheet Functions | |||
Countifs Fx in 07 how in 03? | Excel Discussion (Misc queries) |