How to change column range incrementally as row cell is dragged down?

[flavgf]

Hello all,

I'm trying to figure out how to change the column range incrementally as the cell in column N is dragged down. I'm a little stuck.

The highlighted cells per row should equal the value in column N. The range length stays consistent but moves one to the right as the row moves down.

The value in cell N2 should equal the SUM(B2:C2) and cell N3 should equal SUM(C3:D4)

Any help would be greatly appreciate. Thank you in advance.

 

[Fluff]

Hi & welcome to MrExcel.
Is this for Excel, or some other spreadsheet app?

 

[flavgf]

Hi & welcome to MrExcel.
Is this for Excel, or some other spreadsheet app?

Hello @Fluff - its for excel. Sorry I should have mentioned that.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

No need to mention you are using Excel, I only asked as your screen shot looks more like Google Sheets than Excel.

How about

=SUM(INDEX(B2:M2,ROWS(N$2:N2)+{0,1}))

This may need array entry depending on your version

 

[flavgf]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

No need to mention you are using Excel, I only asked as your screen shot looks more like Google Sheets than Excel.

How about

=SUM(INDEX(B2:M2,ROWS(N$2:N2)+{0,1}))

This may need array entry depending on your version

Hello @Fluff, thank you for your reply.

The formula worked perfectly. Thank you - I really appreciate it. You are right - my screenshot is googlesheets. I tend to work between them but do most of the ground work on excel.

I tried it out in googlesheet but doesn't work even with an array.

Any thoughts on this?

 

[Fluff]

Regarding Sheets, no as I don't use it.
In future if you need to use a formula in both Excel & Sheets, you should make that clear, as they are often very different.

 

[flavgf]

Regarding Sheets, no as I don't use it.
In future if you need to use a formula in both Excel & Sheets, you should make that clear, as they are often very different.

@Fluff thank you for the tips

 

[Fluff]

You're welcome & thanks for the feedback.