Sum between dates-using VBA code

Kellogg

New Member
Great Minds of Excel,

I understand the coding to sum data between dates using sumproduct as a cell formula but how do I write this in VBA code? my scenario: Dates in column A, data (numbers) in column B. Start Date in C1, End date in D1. I want to add up the numbers in column B that fall between the start and end dates which refer to the dates in Column A. The answer needs to be displayed in E1.

Column A .....Column B .....Column C.....Column D.....Column E
2/1...................1.................2/5............2/22.............14
2/5...................2
2/6...................3
2/20.................4
2/22.................5
2/25.................6
2/28.................7

Thanks in advance - CK

Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Peter_SSs

MrExcel MVP, Moderator
Welcome to the MrExcel board!

Since you know the SUMPRODUCT formula method, if you cannot just manually put the formula in E1, one easy way would be to simply get the vba code to put the formula there for you. If you don't want the formula to remain, it is easily replaced with the resultant value.

Try this in a copy of your workbook.
Code:
``````Sub SumDateData()
With Range("E1")
.Formula = "=SUMPRODUCT(--(A1:A1000>=C1),--(A1:A1000<=D1),B1:B1000)"
.Value = .Value
End With
End Sub``````

jim may

Well-known Member
Sorry, Not the VBA version you asked for...

Excel 2010
A
B
C
D
E
1
1-Feb
1
5-Feb
22-Feb
14
2
5-Feb
2
3
6-Feb
3
4
20-Feb
4
5
22-Feb
5
6
25-Feb
6
7
28-Feb
7

<tbody>
</tbody>
Sheet1

Worksheet Formulas
Cell
Formula
E1
=SUMPRODUCT(--(\$A\$1:\$A\$7>=\$C\$1),--(\$A\$1:\$A\$7<=\$D\$1),\$B\$1:\$B\$7)

<tbody>
</tbody>

<tbody>
</tbody>

Last edited:

Kellogg

New Member
Welcome to the MrExcel board!

Since you know the SUMPRODUCT formula method, if you cannot just manually put the formula in E1, one easy way would be to simply get the vba code to put the formula there for you. If you don't want the formula to remain, it is easily replaced with the resultant value.

Try this in a copy of your workbook.
Code:
``````Sub SumDateData()
With Range("E1")
.Formula = "=SUMPRODUCT(--(A1:A1000>=C1),--(A1:A1000<=D1),B1:B1000)"
.Value = .Value
End With
End Sub``````

Peter, This worked perfect. The code has to be in VBA since it is embedded into a larger code. I discovered my error - I was including a ")" after the A1:A1000. Thanks for the help. It is the simple things in life...

Kellogg

New Member
Sorry, Not the VBA version you asked for...

Excel 2010
ABCDE
11-Feb15-Feb22-Feb14
25-Feb2
36-Feb3
420-Feb4
522-Feb5
625-Feb6
728-Feb7

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
E1=SUMPRODUCT(--(\$A\$1:\$A\$7>=\$C\$1),--(\$A\$1:\$A\$7<=\$D\$1),\$B\$1:\$B\$7)

<tbody>
</tbody>

<tbody>
</tbody>

Thanks for the reply anyway! It is nice to get such a quick turn around.

Peter_SSs

MrExcel MVP, Moderator
Peter, This worked perfect.

Here's a little more that may be useful. I had hard-coded the formula for 1000 rows. If the number of rows might be variable and you want to have a formula that just addresses the relevant rows, you could use a structure like this. You should still recognise the basic SUMPRODUCT formula but it has "#" as a placeholder for the last row. So the code finds the last row (lr) then replaces # with that value in the formula. To see how that works you could remove the .Value = .Value line (or just comment it out) and run this new code.
Code:
``````Sub SumDateData_v2()
Dim lr As Long

Const frmla As String = "=SUMPRODUCT(--(A1:A#>=C1),--(A1:A#<=D1),B1:B#)"

lr = Range("A" & Rows.Count).End(xlUp).Row
With Range("E1")
.Formula = Replace(frmla, "#", lr, 1, -1, 1)
.Value = .Value
End With
End Sub``````

Kellogg

New Member

Here's a little more that may be useful. I had hard-coded the formula for 1000 rows. If the number of rows might be variable and you want to have a formula that just addresses the relevant rows, you could use a structure like this. You should still recognise the basic SUMPRODUCT formula but it has "#" as a placeholder for the last row. So the code finds the last row (lr) then replaces # with that value in the formula. To see how that works you could remove the .Value = .Value line (or just comment it out) and run this new code.
Code:
``````Sub SumDateData_v2()
Dim lr As Long

Const frmla As String = "=SUMPRODUCT(--(A1:A#>=C1),--(A1:A#<=D1),B1:B#)"

lr = Range("A" & Rows.Count).End(xlUp).Row
With Range("E1")
.Formula = Replace(frmla, "#", lr, 1, -1, 1)
.Value = .Value
End With
End Sub``````

Peter,

Happy Easter. Your too good.

Okay, I used your formula in a single page but now I have been dreaming on how to incorporate into the following. The code below checks all pages that contains the Fvalue and extracts information from each page. Instead of having one workbook for each record If I combine all worksheets into one workbook I can save files but the code you wrote calcuates well for a single worksheet but if I try to add to the following code between the '******************* where it says get monthly miles it calcuates from the summary page and not the target worksheet. The code between the '^^^^^^^^^^^^^^^^^^ (get total miles) works excellent getting the total using Application.Max(MilesRange) across sheets. This is the model I would like to adapt your sumproduct code to. Any thoughts. Thanks again for your work.

Sub GenerateSummaryReports()
'
'unlock
ActiveSheet.Unprotect
Dim sh As Worksheet
Dim FValue As String
Dim SumSheet As String
'Clear old report
Range("B9:Y47").Select
Selection.ClearContents
'
'What is the name of summary sheet?
SumSheet = "Summary Report"
'Which row in Summary sheet are we starting on?
i = 9
'Which column in summary sheet?
x = "B"
'
FValue = Range("A6").Value
'
'
With Worksheets(SumSheet)
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> SumSheet Then
If ws.Range("P1") = FValue Then
.Cells(i, x) = ws.Range("C11").Value 'Plate
.Cells(i, x).Offset(0, 1) = ws.Range("R4").Value 'Vehicle Type
.Cells(i, x).Offset(0, 5) = ws.Range("R7").Value 'Year
.Cells(i, x).Offset(0, 7) = ws.Range("R3").Value 'Location
.Cells(i, x).Offset(0, 9) = ws.Range("R2").Value 'Assigned
.Cells(i, x).Offset(0, 11) = "Trips" 'ws.Range("D9").Value 'No. of Trips

'get monthly miles***********************************************************
I want to modify your code below to match the style in the section below it between the '^^^^^^^^^

With Range("D7")
.Formula = "=sumproduct(--(C15:C100>=C7),--(C15:C100<=C8),M15:M100)"
.Value = .Value
End With
.Cells(i, x).Offset(0, 12) = "Mon" 'ws.Range("J11").Value 'Miles - Month
'****************************************************************************

'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
'get total miles on vehicle - THIS WORKS
'Create and fill variables
'variables
Dim MilesRange As Range
Dim MaxMiles As Double
lnglast = ws.Range("A" & Rows.Count).End(xlUp).Row

'calculation Ranges
Set MilesRange = ws.Range("L15:L" & lnglast)

'calculations
MaxMiles = Application.Max(MilesRange)
MaxMiles = Format(MaxMiles, "0.0")
.Cells(i, x).Offset(0, 13) = MaxMiles 'ws.Range("J11").Value 'Miles - Tot
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

.Cells(i, x).Offset(0, 15) = "GMon" 'ws.Range("J11").Value 'Gas - Month
.Cells(i, x).Offset(0, 17) = "serO" 'ws.Range("J11").Value 'Service - Oil
.Cells(i, x).Offset(0, 18) = "serC" 'ws.Range("J11").Value 'Service - Cleaned
.Cells(i, x).Offset(0, 20) = "A" 'ws.Range("J11").Value 'Safety Check A
.Cells(i, x).Offset(0, 21) = "B" 'ws.Range("J11").Value 'Safety Check B
.Cells(i, x).Offset(0, 22) = "C" 'ws.Range("J11").Value 'Safety Check C
i = i + 1
End If
End If
Next ws
End With
'
'
End Sub

Peter_SSs

MrExcel MVP, Moderator
When posting code, please make sure your code is indented and preserve that indentation by using Code Tags - see my signature block for how to do that.
It is very hard to read/debug code that is all left-aligned like that.

It is hard to give you exact code advice regarding your query because there are too many things I do not know. For example, I don't know ..
- what the active sheet is when the code is run
- what is in A6 (FValue = Range("A6").Value)
- what is in P1, C11, R2, R3, R4, R7 on each sheet
etc

However, perhaps the following will lead you in the right direction?

I think you will need the type of idea I used in post #6.
In a copy of your workbook ..

Paste this just below your Dim statements at the top of your code. (It is normal to put all your Dim statements at the start of the code)
Rich (BB code):
``Const frmla As String = "=SUMPRODUCT(--('@'!C15:C100>=C7),--('@'!C15:C100<=C8),'@'!M15:M100)"``

Where you are trying to put this formula, on the Summary sheet I think, instead of "WithRange("D7")" you may need something more like "With .Cells(i,x).Offset(..." similar to what you have in other sections of your code.

For the formula itself, replace the red line with the blue line
Rich (BB code):
``````.Formula = "=sumproduct(--(C15:C100>=C7),--(C15:C100<=C8),M15:M100)"
.Formula = Replace(frmla, "@", ws.Name, 1, -1, 1)
.Value = .Value``````

Kellogg

New Member
Peter,

Sorry about the code indents. I was not paying attention to the code rules. Let me explain again.

Your codes work great but I'm trying to not have any codes on the source data worksheets. I want to use VBA code to search between two dates. I do not want to use a formula in a cell. I would like the "=sumproduct(--(C15:C100>=C7),--(C15:C100<=C8),M15:M100)" similar syntax to "Application.WorksheetFunction.CountIf(Range("P:P"), "Subject")"

Here is my scenario. I have several worksheets for individual vehicle data. The data is all vehicle information: miles driven, when cleaned, oil changes, etc. I want to search between dates to add up miles driven between dates specified in C7 and C8 and the miles for each trip in column M. I have one page that will list a summary of all vehicles and the code I want wil grab the data from each worksheet and consolidate on one page. I would like to have no formulas on any individual vehicle worksheet and only use VBA code attached to a button to generate the report on the summary page. I have code presently that pulls information form each sheet and lists on the summary page but can not get the right code to search between dates then count occurrances or sum totals between dates. The references to cells listed in the code are explained next to the cell designation. I have also summarized below.

The active sheet is the summary page. The code looks through all sheets that are named "Vehicle Info" which is stored in "P1" of each worksheet.
"A6" on the summary page is "Vehicle Info". This tells the summary sheet VBA code to look on only sheets that contain Vehicle info in "P1" that equals "A6 from summary sheet.

"C11" is the vehicle liscense plate
"R4" is the Vehicle Type
"R7" is the Vehicle Year
"R3" is the Vehicle Location
"R2" is who the vehicle is assigned tor
Column "M" on the individual vehicle worksheet is where the miles driven are stored.
Column "C" on the individual vehicle worksheet is where the dates are stroed for each vehicle use

All these values do not have a bearing on the VBA code to sum data between dates. In the specific code that I'm looking for it will sum total of the miles driven between two dates. The starting date range is in "C7" and the end date range is in "C8".

I will consolidate the Dims but needed to refer to what they applied to while I work on the coding. My mind is not tuned to VBA yet.

What I have already is the following:

Code:
``````Sub GenerateSummaryReports() ' THIS IS LOCATED ON A SUMMARY PAGE ONLY AND PULLS DATA FROM INDIVIDUAL WORKSHEETS
'
'unlock
ActiveSheet.Unprotect
Dim sh As Worksheet
Dim FValue As String
Dim SumSheet As String
'Clear old report
Range("B9:Y47").Select
Selection.ClearContents
'
'What is the name of summary sheet?
SumSheet = "MI Vehicle Summary Report"
'Which row in Summary sheet are we starting on?
i = 9
'Which column in summary sheet?
x = "B"
'
FValue = Range("A6").Value 'THIS IS TEXT ON EVERY VEHICLE RECORD THAT IDENTIFIES THE WORKSHEET AS A VEHICLE INFO SHEET.  It is just "Vehicle Info" located in cell "P1"
'
'
'
With Worksheets(SumSheet)
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> SumSheet Then
If ws.Range("P1") = FValue Then
.Cells(i, x) = ws.Range("C11").Value 'Plate
.Cells(i, x).Offset(0, 1) = ws.Range("R4").Value 'Vehicle Type
.Cells(i, x).Offset(0, 5) = ws.Range("R7").Value 'Vehicle Year
.Cells(i, x).Offset(0, 7) = ws.Range("R3").Value 'Vehicle Location
.Cells(i, x).Offset(0, 9) = ws.Range("R2").Value 'Assigned to which driver
.Cells(i, x).Offset(0, 11) = "No of Trips" ' I still need to count between dates

[COLOR=#ff0000] 'get monthly miles**********************************************************[/COLOR]  This is where I need to sum between dates.

[COLOR=#ff0000]
With Range("D7")
.Formula = "=sumproduct(--(C15:C100>=C7),--(C15:C100<=C8),M15:M100)"
.Value = .Value
End With
.Cells(i, x).Offset(0, 12) = "Mon" 'ws.Range("J11").Value   'Miles - Month[/COLOR]

'***************************************************************************************

"^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[COLOR=#0000cd] 'get total miles on vehicle - THIS WORKS GREAT
'Create and fill variables
'variables
Dim MilesRange As Range
Dim MaxMiles As Double
lnglast = ws.Range("A" & Rows.Count).End(xlUp).Row

'calculation Ranges
Set MilesRange = ws.Range("L15:L" & lnglast)

'calculations
MaxMiles = Application.Max(MilesRange)
MaxMiles = Format(MaxMiles, "0.0")
.Cells(i, x).Offset(0, 13) = MaxMiles 'ws.Range("J11").Value   'Miles - Tot[/COLOR]
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

.Cells(i, x).Offset(0, 15) = "GMon" 'ws.Range("J11").Value   'Gas - Month
.Cells(i, x).Offset(0, 17) = "serO" 'ws.Range("J11").Value   'Service - Oil
.Cells(i, x).Offset(0, 18) = "serC" 'ws.Range("J11").Value   'Service - Cleaned
.Cells(i, x).Offset(0, 20) = "A" 'ws.Range("J11").Value   'Safety Check A Performed
.Cells(i, x).Offset(0, 21) = "B" 'ws.Range("J11").Value   'Safety Check B Performed
.Cells(i, x).Offset(0, 22) = "C" 'ws.Range("J11").Value   'Safety Check C Performed
i = i + 1
End If
End If
Next ws
End With
'
'
End Sub``````

I hope this helps clear up any details I ommitted. I know we are close. The code you wrote is very useful on my other sheets but tricky on a summary page referring to other sheets.

Craig

Peter_SSs

MrExcel MVP, Moderator
Yes, I think we are getting closer, but there are a few more things to ask/confirm..
... add up miles driven between dates specified in C7 and C8
Can you confirm that C7 & C8 you are referring to here are on the Summary sheet?

.. count occurrances or sum totals between dates.
Which column on the Summary sheet do each of these results go in?
Looks like No of trips is to go in column M - Offset(,11)?
Is it column N - Offset(,12) - for the total between dates?

Column "M" on the individual vehicle worksheet is where the miles driven are stored.
Column "C" on the individual vehicle worksheet is where the dates are stroed for each vehicle use
Confirming these start on row 15 of the individual vehicle sheets?
And that the final row can be determined from column A (lnglast)?

What version(s) of Excel will this be used on?

What is in column L of the individual vehicle sheets?
(Just getting a feel for what your MaxMiles is actually doing)

I hope that is all the questions, but there might be more later.

Replies
2
Views
55
Replies
5
Views
287
Replies
23
Views
350
Replies
7
Views
218
Replies
3
Views
226

1,196,010
Messages
6,012,840
Members
441,733
Latest member
MartijnB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back