got wrong answer Countifs VBA Excel with condition between 2 date

[andrianocaras]

i need a help with my VBA excel . . . i have an excel like the picture above and i was trying countifs with condition between 2 date
in counting i got the right answer 5 but in counting1 i got 8 instead the right answer 5 in counting2 i got 0 instead the right answer 5 or wrong answer 8 just like in counting1, in counting2 i just reverse the condition this is the code

Dim rB As Range
MyDate = CDate(Range("B6"))
MyDate1 = CDate(Range("B2"))
Set rB = Range("B2", Range("B" & Rows.Count).End(xlUp))
LS = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
counting = Application.WorksheetFunction.CountIfs(rB, ">=" & MyDate1, rB, "<=" & MyDate)
counting1 = Application.WorksheetFunction.CountIfs(rB.Offset(0, 1), "<=" & MyDate, rB, ">=" & MyDate1)
counting2 = Application.WorksheetFunction.CountIfs(rB.Offset(0, 1), ">=" & MyDate1, rB, "<=" & MyDate)
MsgBox counting
MsgBox counting1    
MsgBox counting2

counting = i want to count B with condition >=Mydate1 and <=MyDate i got answer 5
counting1 = i want to count C with condition <=Mydate and >=MyDate1 i got answer 8
counting2 = i want to count C with condition >=Mydate1 and <=MyDate i just reverse the condition, i got answer 0
can anyone help me so i can get the right answer ??

 

[jorismoerings]

Hi,

I am not sure what you're trying to acomplish with these countings and your logic is somewhat off.
your VBA part

[COLOR=#000080]Application.WorksheetFunction.CountIfs(rB.Offset(0, 1), "<=" & MyDate, rB, ">[/COLOR]="  & MyDate1)

is creating this formula

=COUNTIFS(C2:C12,"<="&MyDate,B2:B12,">="&MyDate1)

so it checks a dateserialnumber (in this case MyDate) against a set of numbers. As the date serial number for MyDate (B6 = 4-may-11) = 40667 any number in column C will is smaller than 40667 combined with the criteria 2 being the date in column B being greater than B2; this formual will evaluate to 8.

Counting2 will only come to result if your number in column C are greater than 40571 (being the dateserial for B2) combined with a date that's older then 4-may-11

Than solving your issue comes with questions and assumptions first:
1. counting1 - You want to count C with condition <=Mydate and >=MyDate1 and then? eg. Do you need the actual cell filed with numbers or .....
2. counting2 - i assume You want to count all numbers outside of the date selection

try this:

Dim rB As Range
MyDate = Range("B6").Value
MyDate1 = Range("B2").Value
Set rB = Range("B2", Range("B" & Rows.Count).End(xlUp))
LS = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
counting = Application.WorksheetFunction.CountIfs(rB, ">=" & MyDate1, rB, "<=" & MyDate)
counting1 = Application.WorksheetFunction.CountIfs(rB, ">=" & MyDate1, rB, "<=" & MyDate, rB.Offset(0, 1), ">=0")
counting2 = Application.WorksheetFunction.Count(rB.Offset(0, 1)) - counting1
MsgBox counting
MsgBox counting1
MsgBox counting2
End Sub