Multiply all rows with one row

[ddsunil]

Searched hard but couldnt find what I need, so, posting here. Forgive me if this is answered someplace already.

I have an excel file where I need to calculate certain values. This is simple but I am stumped at how to do it.

These are my columns and three rows with each row used for one day.
Enter values for each date here.
Date 1000 500 100 50 20 10 5 Totals
03-Jul 10 20 10 10 10 0 0 21700
04-Jul 11 21 11 11 11 1 1 23385

What I need is a formula to get the totals in the column. The cell across July 3rd for Totals has the formula =B3*B2+C3*C2+D3*D2+E3*E2+F3*F2+G3*G2+H3*H2

The cell across July 4th for Totals has the formula
=B4*B2+C4*C2+D4*D2+E4*E2+F4*F2+G4*G2+H4*H2

If you see the common multiplier is the row 2. From B2 to H2. When I write this formula in row 2, and then copy and paste it in Row 3, the formula changes to =B4*B3+C4*C3+D4*D3+E4*E3+F4*F3+G4*G3+H4*H3

So excel is thinking that I intend to multiply the current row and the previous row and changing all the Row 2 to Row 3. Is there anyway I can work around this and ensure that I dont type these manually for the number of rows I will have in the future. I am sure there is. But I just dont know how to get there.

Thanks so much for your help

S

 

[zaska]

Hi,

Try this

=SUMPRODUCT(B$1:H$1,B2:H2) and drag down

Regards,

Zaska

 

[snoopyhr]

Similar to Zaska's solution with a bit of different syntax.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	1000	500	100	50	20	10	5	Totals		This	Or
2	3-Jul	10	20	10	10	10	0	0	21700		21700	21700
3	4-Jul	11	21	11	11	11	1	1	23385		23385	23385
Sheet2

 

[ddsunil]

Thanks Zaska and Snoopy. It works now

You guys continue to amaze me on this board. Truly awesome.

Thanks again
S