Forum Discussion
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!
- ExcelonlineadvisorIron Contributor
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.
- Anastasia770Copper ContributorIn 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?
- ExcelonlineadvisorIron ContributorIn 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.