quickly repeating long-winded formula

[fairkatrina]

please bear with me with this...

I'm trying to write a spreadsheet for stocktaking where rows 2-40 have the stock items, column C has the price, and then each subsequent column has a different job I've used the stock for, so I can go down the rows adding how many of which item I've used for each job. I want to be able to calculate at the bottom of each column from D onwards the total spend on that individual job, and I've ended up with a very longwinded formula in each cell that goes something like =SUM(D2*C2)+(D3*C3)...and so on until D40!

While I can live with this, to replicate the formula in each column I can only think of copying and pasting it into the next cell in the row, all the way along the row, but each time I do this excel moves all the cells in the formula down one column, which obviously works for the column relating to the specific job (D, E, F etc) but I need column C to stay fixed throughout all the formulas. At the minute the only way I can solve this is to manually change the formula back to column C by dragging the highlighted cells individually, which means I'm repeating this 39x for each column.

Is there *any* way of either making the formula simpler, or being able to drag all 39 highlighted cells that are in the wrong column across to C at once?

 

[sulakvea]

sorry didnt read the whole post (a very annoying coworker is trying to talk to me)... seems like you need SUMPRODUCT(Range1, Range2)

 

[jbeaucaire]

You can add a $ sign in front of any part of any cell reference that you don't want to change as you copy the cell.

A1 = everything is relative, the column and row references will change
$A1 = Column is absolute, row is relative
A$1 = Column is relative, row is absolute
$A$1 = everything is locked as absolute

 

[fairkatrina]

thanks all, I think for this the $ function has cracked it!