Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim J As Range, G As Range, Where As Range, Colu As Range, Fndrow As Range
Dim Low As Date, High As Date
Dim Dates, Types, Values
Dim i As Long
Dim Sum As Double, Sum1 As Double, Sum2 As Double
'Only if P1 changes
If Intersect(Target, Range("P1")) Is Nothing Then Exit Sub
'Get the dates
Low = Range("EO1").Value
High = Range("EN1").Value
'Refer to the used cells in column J
Set J = Range("J8", Range("J" & Rows.Count).End(xlUp))
'Same size in column G
Set G = Intersect(Columns("G"), J.EntireRow)
'Read in all data
Dates = J.Value
Types = G.Value
Select Case Target.Value
Case Range("EN2").Value
'Same size in column CF
Set Where = Intersect(Columns("CF"), J.EntireRow)
'Read in all values
Values = Where.Value
'Process the SUMPRODUCT
For i = 1 To UBound(Dates)
If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = "Sales" Then
Sum = Sum + Values(i, 1)
End If
Next
Case Range("EN3").Value
Set Where = Intersect(Columns("T"), J.EntireRow)
Values = Where.Value
For i = 1 To UBound(Dates)
If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = "Sales" Then
Sum = Sum + Values(i, 1)
End If
Next
Case Range("EN4").Value
Set Where = Intersect(Columns("B"), J.EntireRow)
Dates = Where.Value
Set Where = Intersect(Columns("CF"), J.EntireRow)
Values = Where.Value
For i = 1 To UBound(Dates)
If Dates(i, 1) <= High Then
Sum1 = Sum1 + Values(i, 1)
End If
Next
Set Where = Intersect(Columns("CA"), J.EntireRow)
Values = Where.Value
Dates = J.Value
For i = 1 To UBound(Dates)
If Dates(i, 1) <= High Then
Sum2 = Sum2 + Values(i, 1)
End If
Next
Sum = Sum1 - Sum2
Case Range("EN5").Value
Set Where = Intersect(Columns("BY"), J.EntireRow)
Values = Where.Value
'Process the SUMPRODUCT
For i = 1 To UBound(Dates)
If Dates(i, 1) >= Low And Dates(i, 1) <= High And Types(i, 1) = "Sales" Then
Sum = Sum + Values(i, 1)
End If
Next
Case Range("EN6").Value
For Each Colu In Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp))
If Colu.Value = High Then
Set Fndrow = Colu
Exit For
ElseIf Colu.Value > High Then
Set Fndrow = Colu.Offset(-1)
Exit For
End If
Next Colu
If Fndrow Is Nothing Then
Set Fndrow = Sheet2.Range("A" & Rows.Count).End(xlUp)
End If
For J = 85 To 136
For i = 2 To 53
Sum = Sum + Cells(4, i).Value * Fndrow(1, i).Value
Next i
Next
Sum = Sum / 52
Case Range("EN7").Value
Set Where = Intersect(Columns("B"), J.EntireRow)
Dates = Where.Value
Set Where = Intersect(Columns("CE"), J.EntireRow)
Values = Where.Value
For i = 1 To UBound(Dates)
If Dates(i, 1) <= High Then
Sum1 = Sum1 + Values(i, 1)
End If
Next
Set Where = Intersect(Columns("BZ"), J.EntireRow)
Values = Where.Value
Dates = J.Value
For i = 1 To UBound(Dates)
If Dates(i, 1) <= High Then
Sum2 = Sum2 + Values(i, 1)
End If
Next
Sum = Sum1 - Sum2
End Select
'Events off, otherwise we call ourself
Application.EnableEvents = False
'Write the sum into the sheet
Range("EL7") = Sum
'Events on
Application.EnableEvents = True
End Sub