Summary Question

[xclaim]

I need a quick way to calculate hrs*rate for a period.
I normally do it with a formula like '=(E3*$D$3)+(E4*$D$4)'
but its not scalable and if i add rows in between the calculation is thrown off.
any easy way to do this ?

image1
https://ibb.co/bUW8B5
is my table

image2
https://ibb.co/fe2yB5
is the formula (not complete -- i woudl actually need to expand the formula to every row/user)

Pls help

 

[Chrisdontm]

I think I see what you are wanting....

Try this formula in column "E" on the subtotal row;

=SUMPRODUCT(OFFSET($D$2,1,,COUNTA($C:$C),1),OFFSET(E$2,1,,COUNTA($C:$C),1))

Then copy it over to the right as far as you need to...

(When using the OFFSET formula to define your ranges, you can insert a row or delete a row and it will still work...)

 

[Peter_SSs]

Perhaps I have misunderstood, but don't you just need something like this, copied across?

Book1
	C	D	E	F	G
2		rate	week1	week2	week3
3	user1	200	5	6
4	user2	200	6	3
5	user3	200			1
6	user4	300	2
7	user5	300
8	user6	300			5
9	user7	400
10	user8	400
11	user9	400		2
12			2800	2600	1700
Totals

Cell Formulas
Range	Formula
E12	=SUMPRODUCT($D3:$D11,E3:E11)

 

[Chrisdontm]

Peter, I believe you are right.
It appears that the SUMPRODUCT formula is not effected by adding or deleting rows like a lot of formulas, I never noticed...

 

[xclaim]

Perhaps I have misunderstood, but don't you just need something like this, copied across?

	C	D	E	F	G
2		rate	week1	week2	week3
3	user1	200	5	6
4	user2	200	6	3
5	user3	200			1
6	user4	300	2
7	user5	300
8	user6	300			5
9	user7	400
10	user8	400
11	user9	400		2
12			2800	2600	1700

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Totals​

Worksheet Formulas Cell Formula E12 =SUMPRODUCT($D3:$D11,E3:E11) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

awesome - that works perfectly

i added to it and now this formula works perfectly for me

=SUMPRODUCT($C$6:$C$39,I6:I39)

thank you so much.

 

[xclaim]

Peter, I believe you are right.
It appears that the SUMPRODUCT formula is not effected by adding or deleting rows like a lot of formulas, I never noticed...

yes that's correct

 

[Peter_SSs]

awesome - that works perfectly

Great, thanks for letting us know.