You can use the following syntax in VBA to calculate the sum if cells are between two dates:

Sub SumifBetweenDates()
Range("E3") = WorksheetFunction.SumIfs(Range("B2:B9"), Range("A2:A9"), ">=" & [E1], _
                                                       Range("A2:A9"), "<=" & [E2])
End Sub

This particular example will sum the values in the range B2:B9 only where the date in the range A2:A9 is between the start date in cell E1 and the end date in cell E2.

The sum will then be shown in cell E3.

The following example shows how to use this syntax in practice.

Example: How to Sum If Between Two Dates in VBA

Suppose we would have the following dataset that contains information about the total products sold on various dates at some retail store:

Suppose we would like to calculate the sum of sales only for the dates between 1/7/2023 and 1/26/2023.

We can create the following macro to do so:

Sub SumifBetweenDates()
Range("E3") = WorksheetFunction.SumIfs(Range("B2:B9"), Range("A2:A9"), ">=" & [E1], _
                                                       Range("A2:A9"), "<=" & [E2])
End Sub

When we run this macro, we receive the following output:

Notice that cell E3 contains a value of 16.

This represents the sum of values in the sales column where the date is between 1/7/2023 and 1/26/2023.

We can verify that this is correct by manually calculating the sum of values in the Sales column where the corresponding value in the Date column is between 1/7/2023 and 1/26/2023:

Sum of Sales: 3 + 7 + 6 = 16.

Note that if you change the start and end date values in cells E1 and E2, respectively, and run the macro again that it will calculate the sum of sales for the new dates.

Note: It is assumed that the values in the date column are already formatted as dates in Excel.