VBA SumIfs not working with Date range

Question

So, I have tried many variations, but I can't make this work:

var_sum = WorksheetFunction.SumIfs(Range("H:H"), Range("B:B"), str_client, Range("A:A"), "<=" & date_var)

This formula has 2 conditions. The second one (date comparison) breaks it and makes the sum = 0. date_var is a Date variable. The date range in Excel is formatted as Date.

What could be wrong?

Answer 1

I have a feeling you have a string with the date.

Date_Var = "01/01/2013"

you could try using CDate( or CLng( to convert it to the appropriate value, as Excel stores dates as number of days past Jan 1, 1900

var_sum = WorksheetFunction.SumIfs(Range("H:H"), Range("B:B"), str_client, _
    Range("A:A"), "<=" & CLng(date_var))