SUM OFFSET help?

[Bravo2003]

Hi All,
I've not used OFFSET before so am struggling to work out what I need to do with this requirement:

I have a sheet with sets of 6 cells that need to be summed up.

example:
SHEET1 - has COLUMN N - ROWS 8-13 with numbers in
then another 6 rows from 15-20, 22-27, etc...
Then this repeats in every 9th Column (so in X, AH, AR, etc..)

In SHEET2 I need:
I5 to sum the first set of 6 cells - N8:N13
J5 to sum the next 6 cells under that - N15-N20
and so on

Whilst in Row 6 it needs to sum the 6 cells 9 columns along
so I6 sums X8:X13
and repeat down and across

Any clues on how to do this without writing 100's of different SUM formula in each cell required?

 

[Bravo2003]

So in B4, copied down, try

=COUNTIF(INDEX(Sheet1!N$1:DI$1,ROWS(B$4:B4)*10-9):INDEX(Sheet1!N$1:DI$500,300,ROWS(B$4:B4)*10-9),5)

Peter, Thank you so much for helping.

afraid I made another mistake... the formula in B4 needs copying across, not down..
so B4 / C4 / D4 etc..

what adjustment to your formula should I make?

 

[Peter_SSs]

the formula in B4 needs copying across, not down..
so B4 / C4 / D4 etc..

what adjustment to your formula should I make?

=COUNTIF(INDEX(Sheet1!$N$1:$DI$1,COLUMNS($B4:B4)*10-9):INDEX(Sheet1!$N$1:$DI$500,300,COLUMNS($B4:B4)*10-9),5)