sumproduct

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shaji - 17 May 2008 13:34 GMT

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

Reply to this Message

Gary''s Student - 17 May 2008 14:11 GMT

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

Signature

Gary''s Student - gsnu200786

> Hi
>
[quoted text clipped - 10 lines]
>
> thanks in advance

Reply to this Message

Prashant Runwal - 18 May 2008 12:25 GMT

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

> Hi
>
[quoted text clipped - 10 lines]
>
> thanks in advance

Reply to this Message