Search code examples
excelgoogle-sheetsautocompletegoogle-sheets-formulaautofill

Autofill formular, but constrain one or more cells - Google sheets

I'm trying to make a google sheets spreadsheet where I calculate the salary for employees based on their hours spent on each project and hourly wage for each project. Each project pay a different amount. To calculate their salary I need to multiply each hourly wage with the hours spent (for each project) and then sum the amount.

How to automate this? enter image description here

As seen in the linked image, the hourly wage calculated for each row and then added together. If I try to autofill this formular by clicking and dragging, the hourly wage values are dragged with the rest, which is wrong. I want to constrain the hourly wage values and just drag the hours values. See the next image for what the problem is.

These values should stay, the others should move: enter image description here

How can I achieve this? Will I have to use other functions or get into custom code?

Solution

  • Use absolute reference for `Column B like

    =$B3*E3+$B4*E4+$B5*E5+$B6*E6

    You can also use SUMPRODUCT() to get result directly without summing manually. Try-

    =SUMPRODUCT($B$3:$B$6,E3:E6)

    Reference: