Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Excel

Copper Contributor

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!

3 Replies

@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.

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?
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.