Summary based on channel & date ranges
 

Hi,

In sheet1 I have Row 7 as header. From row 8 there is data.
In header have items in columnB, quantity in columnF, channel in
columnH & date in column I.


I want a routine which will give me a summary in sheet2 based on
channel selected and start/end date.
--------------------------------------------
Channel:<Select
Start Date:<Select
End Date:<Select


Items Quantity
A 10
B 30
C 60
D 90
E 80
F 70
--------------------------------------------

The quantity is sum of quantity date wise/channel wise.

Summary based on channel & date ranges
 

Sub make_summary()
StartDate = DateValue("1/1/08")
EndDate = Date 'today
ChanNum = 22

TotQuant = 0
With Sheets("Sheet1")
RowCount = 8
Do While .Range("B" & RowCount) < ""
If .Range("B" & RowCount) = ChanNum And _
.Range("I" & RowCount) = StartDate And _
.Range("I" & RowCount) <= EndDate Then

TotQuant = TotQuant + .Range("I" & RowCount)
End If
RowCount = RowCount + 1
Loop
End With
With Sheets("Sheet2")
Sht2LastRow = .Range("A" & Rows.Count). _
End(xlUp).Row
If Sht2LastRow = 1 Then
.Range("A1") = "Channel"
.Range("B1") = "Quantity"
Sht2NewRow = 2
Else
Sht2NewRow = Sht2LastRow + 1
End If
.Range("A" & Sht2NewRow) = ChanNum
.Range("B" & Sht2NewRow) = TotQuant

End With

End Sub


"Sinner" wrote:

Hi,

In sheet1 I have Row 7 as header. From row 8 there is data.
In header have items in columnB, quantity in columnF, channel in
columnH & date in column I.


I want a routine which will give me a summary in sheet2 based on
channel selected and start/end date.
--------------------------------------------
Channel:<Select
Start Date:<Select
End Date:<Select


Items Quantity
A 10
B 30
C 60
D 90
E 80
F 70
--------------------------------------------

The quantity is sum of quantity date wise/channel wise.