sum every 6th line from another column

[MAP77]

Hi there,

I need to convert my 5 min interval data into 30 mins by adding up all the 5 min values in the 30 min time slot.
Eg. The 6 highlighted values in column D need to sum in column J. Note: I will add up the values for column D2 to D6 myself as its not a complete 30 mins.

 

[MAP77]

Thanks for the profile update - & the XL2BB sample data!

One way would be to slightly modify your F3 formula to the following and copy down

=SUM(INDEX(G$3:G$52418,SEQUENCE(6,,ROWS(F$3:F3)*6-5)))

Thanks Peter,
Can you help me understand the logic behind the 6-5?
Regards,
Anita

 

[Peter_SSs]

Can you help me understand the logic behind the 6-5?

Sure. The SEQUENCE function is returning 6 numbers since it is 6 cells that you want to sum.
ROWS(F$3:F3)*6-5 is the 3rd argument of SEQUENCE and that is the starting value for the 6 numbers

For the formula in F3 ROWS(F$3:F3)*6-5 is exactly that so it gives 1*6-5 = 1 so the 6 numbers are 1,2,3,4,5,6 which sums the first 6 cells in the range G$3:G$52418
For the formula in F4 it becomes ROWS(F$3:F4)*6-5 = 2*6-5 = 7 so the 6 numbers are 7,8,9,10,11,12 thereby summing the next 6 cells
In F5: ROWS(F$3:F5)*6-5 = 3*6-5 = 13 -> 13,14,15,16,17,18
etc