Forum Discussion

Anastasia770's avatar
Anastasia770
Copper Contributor
Nov 25, 2023

Excel

hello. such a question. how can I make the sum of certain cells in the next row be filled in automatically? For example, the formula for one cell is as follows: =SUMS('Form Responses 1'!P2;'Form Responses 1'!S2;'Form Responses 1'!AA2;'Form Responses 1'!AF2;'Form Responses 1'!AQ2;'Form Responses 1'!AV2;'Form Responses 1'!BC2;'Form Responses 1'!BD2;'Form Responses 1'!BL2;'Form Responses 1'!CU2;'Form Responses 1'!DL2). And the formula for the next cell is as follows: =SUMS('Form Responses 1'!P3;'Form Responses 1'!S3;'Form Response 1'!AA3;'Form Responses 1'!AF 3;'Form Responses 1'!AQ3;'Form Responses 1'!AV3;'Form Responses 1'!BC3;'Form Responses 1'!BD3;'Form Responses 1'!BL3;'Form Responses 1'!CU3;'Form Responses 1'!DL3). That is, as you can see, they differ by the next number in turn. Is there any way to simplify the calculation so that the following lines are counted automatically? Thanks!

  • Anastasia770 

    Let's assume your first formula is in cell A2.

    =SUMS('Form Responses 1'!P2, 'Form Responses 1'!S2, 'Form Responses 1'!AA2, 'Form Responses 1'!AF2, 'Form Responses 1'!AQ2, 'Form Responses 1'!AV2, 'Form Responses 1'!BC2, 'Form Responses 1'!BD2, 'Form Responses 1'!BL2, 'Form Responses 1'!CU2, 'Form Responses 1'!DL2)


    Now, if you want to copy this formula to the cell A3 and have it automatically adjust to the next row, you can change the formula to:

    =SUMS('Form Responses 1'!P3, 'Form Responses 1'!S3, 'Form Responses 1'!AA3, 'Form Responses 1'!AF3, 'Form Responses 1'!AQ3, 'Form Responses 1'!AV3, 'Form Responses 1'!BC3, 'Form Responses 1'!BD3, 'Form Responses 1'!BL3, 'Form Responses 1'!CU3, 'Form Responses 1'!DL3)


    Now, when you copy this formula to the next cell (A4), it will automatically adjust to use row 4 in each of the references. Excel and Google Sheets will automatically increment the row number when you drag or copy the formula down to the next row.

    This way, you don't need to manually change the row numbers in each formula; it will adapt based on its relative position.

    • Anastasia770's avatar
      Anastasia770
      Copper Contributor
      In this case, are there any rules for copying and pasting a formula into the next line? Or for what reasons the cell numbers of the next row may not be automatically substituted in the next row?
      • Excelonlineadvisor's avatar
        Excelonlineadvisor
        Iron Contributor
        In this case, are there any rules for copying and pasting a formula into the next line? -- Depends on your sheet and your expected results. Excel and Google Sheets will automatically increment the row number when you drag or copy the formula down to the next row.

Share