Jump over a row at intervals when dragging down a formula

0
votes

i want to drag down the below formula into row, sum the first 46 rows but i want it to skip four rows (jump over it not delete).

the formula I have is SUM(OFFSET(B$3,(ROW()-3)*46,0,46,1)).... but this keep summing every 46 rows

1
excelexcel-2010
so you want to sum B3:B48, then then next row to sum B52:B97? – Scott Craner
exactly @Scott Craner – Akoma Dupsy Blessing
Change the *46 to *49 – Scott Craner
Just an FYI: Offset is Volatile, Use =SUM(INDEX(B:B,(ROW(A1)-1)*49+3):INDEX(B:B,(ROW(A1)-1)*49+48)) – Scott Craner
Thanks.. but its still adding up the rows i want it to skip or jump – Akoma Dupsy Blessing

1 Answers

1
votes

For sake of answering. Using OFFSET() the jump is determined by the (ROW()-3)*46. By changing the *46 to the correct jump of *49 it will skip the desired rows:

=SUM(OFFSET(B$3,(ROW()-3)*49,0,46,1))

But that is volatile. Volatile functions calculate every time Excel calculates whether the data to which the formula points changes or not. By using INDEX to create the range we can avoid the volatility:

 =SUM(INDEX(B:B,(ROW(A1)-1)*49+3):INDEX(B:B,(ROW(A1)-1)*49+48))

Same thing here the *49 is the jump and the +3 is the starting row of the first group and the +48 is the ending row of the first group.

Both formula will sum the following ranges as it is dragged down: B3:B48,B52:B97,B101:B146,B150:B195,B199:B244

As per the comments use this formula:

=SUM(INDEX(B:B,INT(ROW(B1)/2)*49+MOD((ROW(B1)-1),2)*2+(ISODD(ROW(A1))*48)):INDEX(B:B,INT(ROW(B1)/2)*49+MOD((ROW(B1)-1),2)*2+(ISODD(ROW(A1))*48)))

Which now returns the sums of (B3:B48,B49:B51,B52:B97,B98:B100,B101:B146,B147:B149) as it is filled down.