Sum formula with relative reference

BenIrwin

New Member
Joined
Sep 7, 2007
Messages
10
I am trying to create a simple formula that will add a group of cells in sections of 4 rows. I.E. cell E2 should be =sum(D2:D5). Cell E3 should be =sum(D6:D9). Cell E4 should be =sum(D10:D13) and so on. How to I make this formula so it can fill a series like that? Please know I am working with a sheet of over 30k entries.
Thank you,
Ben
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I am trying to create a simple formula that will add a group of cells in sections of 4 rows. I.E. cell E2 should be =sum(D2:D5). Cell E3 should be =sum(D6:D9). Cell E4 should be =sum(D10:D13) and so on. How to I make this formula so it can fill a series like that? Please know I am working with a sheet of over 30k entries.
Thank you,
Ben

E2, copied down:

=SUM(INDEX(D$2:D$1000,(ROWS(E$2:E2)-1)*4+1):INDEX(D$2:D$1000,(ROWS(E$2:E2))*4))
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,511
Hi Ben,

Try:
=SUM(OFFSET($D$1,(ROW()-2)*3+1,0,3))
Put this in E2 and copy down as far as needed.

Cheers
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hi Ben,

Try:
=SUM(OFFSET($D$1,(ROW()-2)*3+1,0,3))
Put this in E2 and copy down as far as needed.

Cheers

Not robust... Try to insert some rows before the data/formula row.
 

BenIrwin

New Member
Joined
Sep 7, 2007
Messages
10

ADVERTISEMENT

Aladin
That worked great!
Any chance you can explain to me what the heck you did. If not can you give me a keyword that I can search the forum to find out more about it?
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,511
Aladin,

Apart from an error that had my formula looking at only 3 rows at a time instead of 4, it works perfectly well for the problem posed by the OP. FWIW, the formula should have been:
=SUM(OFFSET($D$1,(ROW()-2)*4+1,0,4))

Cheers
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Hi Macropod

You are right, your formula does, in fact, work perfectly for the problem posed by OP. Aladin's comment means that you can improve it. In this case in 2 ways: make it robust and non-volatile.

- It's not robust. This means that it's dependent on the specific position of the data.
Ex.: Suppose you want to add some info before the table. If you select row 1 and insert some rows to write that information, your formula will no longer work, because it was dependant on the table starting in row 2. Or if you decide to have the summary elsewhere and select the formulas in E2:E7K and move it to start in, for ex., F5. The formulas will stop working for the same reason. If you try Aladin's formula you find that it's indiferent to you moving the data around.

- It uses volatile functions. As you know some formulas are always recalculated every time the worksheet calculates. Offset() and Row() are 2 of them as are Now() or Rand(). This means that if, for ex., you have in the sheet a formula like =H1+1, each time you change H1, the worksheet calculates, and your 7K+ formulas will also recalculate although they may have nothing to do with it.

Best regards
PGC
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,511
Hi PGC,

I question whether making the formula more complex than it needs to be is an improvement, especially when it suffers the same kind of robustness issues mine is claimed to suffer from. Aladin's formula stops working once data row 998 is passed - the OP has "over 30k entries". So much for robustness! My version works for as many rows as the sheet has data.

As for the volatility issue, if you time Excel's recalculation of both solutions, I think you'll find there's little in it.

Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top