Is there a way to sum the values between two dates in excel with a formula in VBA?

By /

VBA is a programming language that can be used in Microsoft Excel to automate tasks, such as summing data between two dates. With VBA, you can write a macro to query the table of dates and values, and then sum the values that fall within the start and end dates. VBA is a powerful tool that can provide a more efficient and accurate way to sum data between two dates than a traditional formula.

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. 

 

x

Related terms:

  1. How do I sum a column if the value in that column is less than a given value?
  2. “How do I calculate the number of days between two dates in VBA?”
  3. What is the sum of the values of the “likes” field for all documents in the video.movieDetails collection with a runtime between 90 and 120 minutes, inclusive?
  4. # How do I sum values in a range in VBA?
  5. Excel: Calculate Years & Months Between Two Dates
  6. Excel: Count Number of Days Between Two Dates Inclusive
  7. Excel: Calculate Number of Quarters Between Two Dates
  8. How to Check if Month and Year Match Between Dates in Excel
  9. How do I calculate the number of weeks between two dates in Excel?
  10. How to calculate the difference between two dates in Excel?