Sumifs will not work with Evaluate or Application.WorksheetFunction.SumIfs HELP!

[razzandy]

Hi Guys (Soon be Christmas) ??

I am traying to perform a Sumifs in VBA between two dates. If I use a standard formula on a sheet its works great:

=SUMIFS(B:B,A:A,">=17-11-2020",A:A,"<=23-11-2020")

. This gives me 98.

If I use:

rTotLL = rTot.Evaluate("SUMIFS(B:B,A:A,"">=17-11-2020"",A:A,""<=23-11-2020"")")

I get Zero!

If I use:

rTotLL = Application.WorksheetFunction.SumIfs(.Range("b2:b" & RW), .Range("a2:a" & RW), ">=17-11-2020", .Range("a2:a" & RW), "<=23-11-2020")

I still get Zero!

Any ideas what I'm doing wrong?

Have a good Weekend Guys ?????

 

[Fluff]

Because VBA is US centric, you need to use US style dates

Evaluate("SUMIFS(B:B,A:A,"">=11-17-2020"",A:A,""<=11-23-2020"")")

 

[razzandy]

Because VBA is US centric, you need to use US style dates

Evaluate("SUMIFS(B:B,A:A,"">=11-17-2020"",A:A,""<=11-23-2020"")")

What the Bleeeep, Bleep, Bleeeep, Bleeep!

Thanks Fluff you have saved the day yet again. How do you retain all of this info?

OK, now I know that, the actual dates are produced earlier in a strings named PD and SD. So will I have to reformat both dates using format(PD, "dd-mm-yyyy") and place in the formula like below?:

Evaluate("SUMIFS(B:B,A:A,"">=""&" & Format(SD, "mm-dd-yyyy") & ",A:A,""<=""&" & Format(PD, "mm-dd-yyyy") & ")")

 

[Fluff]

You can try that, I prefer to use numbers rather than dates as I find it safer.
So with those two dates it would be

Evaluate("SUMIFS(B:B,A:A,"">=44152"",A:A,""<=44158"")")

 

[razzandy]

You can try that, I prefer to use numbers rather than dates as I find it safer.
So with those two dates it would be

Evaluate("SUMIFS(B:B,A:A,"">=44152"",A:A,""<=44158"")")

Are those numbers what they call Unicode dates? How would I convert 18-12-2020 to Unicode?

 

[Fluff]

In Xl dates are just numbers with a special formatting. 1st Jan 1900 is 1 2nd Jan is 2 & so on.
How are you getting the date strings?

 

[razzandy]

Are those numbers what they call Unicode dates? How would I convert 18-12-2020 to Unicode?

Arrrrr no, they are called serial numbers of dates. So I need to use this I think within my formula: clng(SD) and clng(PD)

 

[Fluff]

Give it a go & see what happens.

 

[razzandy]

In Xl dates are just numbers with a special formatting. 1st Jan 1900 is 1 2nd Jan is 2 & so on.
How are you getting the date strings?

I get the dates from the below code in combination with the sheet screenshot below.

Global PD As Date 'Postage Date/Shipping Date
Global PD2 As Date
Global SD As Date 'Start Date, when fist bacth run over weekend or bank Hol
Global SD2 As Date

Sub ChkWeekendDate() 'Checks if booking dat eis at weekend and moves t monday
Sheets("ProcessDate").[c1] = Format(Date, "YYYY")

If Weekday(Date, 2) = 6 Then
PD = Date + 2
ElseIf Weekday(Date, 2) = 7 Then
PD = Date + 1
Else
PD = Date
End If
Call ChkBankHols
End Sub

Sub ChkBankHols() 'Checks if booking Date is a bank hol and moves to next day in work
Dim BankHol As Range
PD2 = Date

For Each BankHol In Sheets("ProcessDate").Range("$B$2:$B$11")

If PD2 = BankHol Then

PD2 = BankHol + BankHol.Offset(, 4).Value2
End If

Next BankHol

If PD2 > PD Then PD = PD2

End Sub


Sub ShipTotalStartDateWeekend() 'Creates a start Date if booking at weekend _
    so I can everage totals booked all over a weekend
    
    Sheets("ProcessDate").[c1] = Format(Date, "YYYY")

If Weekday(Date, 2) = 1 Then
SD = Date - 2
ElseIf Weekday(Date, 2) = 7 Then
SD = Date - 1
Else
SD = Date
End If
Call ShipTotalStartDateBankHol
End Sub

Sub ShipTotalStartDateBankHol() 'Creates a start Date if booking on a bank _
    holiday so I can everage totals booked
    
Dim BankHol As Range
SD2 = Date

For Each BankHol In Sheets("ProcessDate").Range("$e$2:$e$11")

If SD2 >= BankHol.Offset(, -1).Value2 And SD2 <= BankHol Then

SD2 = BankHol.Offset(, -3).Value2
End If

Next BankHol

If SD2 < SD Then SD = SD2
End Sub

 

[Fluff]

In that case I would simply change the declaration from Date to Long