Taking a value from another sheet and multiplying (cost x hours) and combining days, weeks months

[angmocummer]

Hello everyone,

I have tried to make a sheet which has

Resource, Task, Rate (I'd like this to be on another sheet, as it does not help here and is a lot of repetition)
Also, right now the columns for hours are based on weeks, but i'd like to do it by days and then collapse by weeks, and months and get totals by weeks and months too. I have no clue how to do that.
Then, I'd like to multiply the rate by the hours to find the total number of hours, however the way it is done right now is not very intelligent and very time consuming when creating the formula, which looks something like this =SUM(H4*$C$4,H5*$C$5,H6*$C$6,H8*$C$8,H9*$C$9,H10*$C$10,H11*$C$11,H13*$C$13,H14*$C$14,H15*$C$15,H16*$C$16)

Can you please give me some pointers as to what can be done to make this a bit better?

Attachment for reference.

Thanks,
G

 

[cyrilbrd]

change formula in D27 from =SUM(D4*$C$4,D5*$C$5,D6*$C$6) to =SUMPRODUCT($C$4:$C$24,D4:D24) copied right till needed.

 

[cyrilbrd]

You could as well create a table in sheet Rate instead of just a list, thus would allow more dynamism, and any added data could be seen in first sheet.

Excel 2010
	A	B
1	Code	Rate
2	SC1	340
3	SC2	340
4	ONSC1	1200
5	ONC1	900
6	GDN CON1	295
7	GDN AN1	220
Rates

Formula in cell C4 would then be =IFERROR(INDEX(Table2[Rate],MATCH(angmocummer!$A4,Table2

,0)),"Not available")

 

[angmocummer]

Whoa!! That was great, and I learned SUMPRODUCT and the idea to do it through a table was very helpful too!!
Many thanks good sir! I tip my internet hat to you

 

[cyrilbrd]

Whoa!! That was great, and I learned SUMPRODUCT and the idea to do it through a table was very helpful too!!
Many thanks good sir! I tip my internet hat to you

Most welcome, thanks for the feedback.