sumproduct

[shaji]

Hi

I am getting a file of prog. details daily in col.B contais Time Period,
col.G contains duration. The file contains data of prog. from morning to
night and I want sum the duration within each hour.

I put 6,7.....23 in K2:K19 and put the following formula in M2 and copied it
down.
=SUMPRODUCT(($G$2:$G$557)*(--(LEFT($B$2:$B$557,2))=K2))

the range will vary daily and i would like to have a macro for the above
calculation which i can run on arrival of the data.

thanks in advance

 

[Gary''s Student]

Sub rakashi()
i = 6
For k = 2 To 19
Cells(k, "K").Value = i
i = i + 1
Next
n = Cells(Rows.Count, "B").End(xlUp).Row

Set rr = Range("M2")
Set r = Range("M3:M19")

rr.Formula = "=SUMPRODUCT(($G$2:$G$" & n & ")*(--(LEFT($B$2:$B$" & n &
",2))=K2))"
rr.Copy r
End Sub

 

[Prashant Runwal]

It is always better to use a structured file. Pls follow following tips

1) Say when you receive your file today your data is in b2:g557. Now create
your own template file in the same format as per format of file you receive.
In your template file mark row no 558 (after your data) in some colour. Now
instead of $g$2:$g$557 use $g$2:$g$558 and same for b. next day when you
receive file, say with 660 rows, then insert 103 additional rows above row no
558 (660-557). Now your formula will automatically get changed to $g$2:$g$661