Glad it helped.

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