Need to sum every 7 columns in Excel worksheet

Question

I have a data set which has 1 ROW and 365 COLUMNS of data. I need to sum the data for every block of 7 columns which I will put into different rows or columns. I realize that 7 does not divide into 365 evenly and am not sure how to account for this or if the correct formula can do this automatically.

B6 is the first cell with data and NB6 is the last cell with data. I'm entering my formula in B14 and can proceed across 51 more columns or rows (doesn't matter).

So far I have been able to successfully sum the first 7 columns with the following formula:

=SUM(OFFSET($B$6,(COLUMN()-COLUMN($NB$6))*0,0,1,7))

However, when copying the formula into successive columns or rows, the formula continues to provide the same value instead of sums for the following 7 days blocks.

Any help would be greatly appreciated. Thanks!

Answer 1

Use this formula:

=SUM(INDEX($6:$6,((ROW(1:1)-1)*7)+2):INDEX($6:$6,((ROW(1:1)*7)+1)))

And drag down.

For columns use:

=SUM(INDEX($6:$6,((COLUMN(A:A)-1)*7)+2):INDEX($6:$6,((COLUMN(A:A)*7)+1)))

And copy across.