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]

Right, I've managed to work out how to sum the different N column requirements

=SUM(OFFSET('SHEET2'!$N$8,0+I$1,0,6,1)) - row1 from I is 0+7 auto populated along

now I nee to get the column reference to move over 9 columns each row?

 

[Peter_SSs]

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

There is a little confusion there, as 9 columns along from column N is column W, not column X.
I am assuming that you want 10 columns along (column X)

In cell I5 of Sheet2, try this formula and copy across and down. You will first need to adjust the two large ranges in the formula (marked blue below) to match your range in Sheet1

=SUM(INDEX(Sheet1!$N$8:$DI$56,COLUMNS($I5:I5)*7-6,ROWS(I$5:I5)*10-9):INDEX(Sheet1!$N$8:$DI$56,COLUMNS($I5:I5)*7-1,ROWS(I$5:I5)*10-9))

Note: I have avoided the OFFSET function as it is a volatile function so could possibly impact the performance of your sheet.

 

[Joe4]

now I nee to get the column reference to move over 9 columns each row?

The secret is to make use of the COLUMN() function, which returns the column number that the formula is placed in.

So, if your first formula is in I5 (column 9), the second is in J5 (column 10), ...
Then the offset from the original N8 would be:
(COLUMN()-9)*9


EDIT: I got distracted while typing this up and missed Peter's response

 

[Bravo2003]

There is a little confusion there, as 9 columns along from column N is column W, not column X.
I am assuming that you want 10 columns along (column X)

In cell I5 of Sheet2, try this formula and copy across and down. You will first need to adjust the two large ranges in the formula (marked blue below) to match your range in Sheet1

=SUM(INDEX(Sheet1!$N$8:$DI$56,COLUMNS($I5:I5)*7-6,ROWS(I$5:I5)*10-9):INDEX(Sheet1!$N$8:$DI$56,COLUMNS($I5:I5)*7-1,ROWS(I$5:I5)*10-9))

Note: I have avoided the OFFSET function as it is a volatile function so could possibly impact the performance of your sheet.

Thanks, that works, but as Sheet1 will be an expanding file, it might not be the best solution needing to change the range references every time ?

But you are right bout 10 columns not 9... this is my error, and I have now resolved the OFFSET formula to work

=IF($C6<>"",SUM(OFFSET('Sheet1'!$N$8,0+H$1,0+$A6,6,1)),"")

 

[Peter_SSs]

Thanks, that works, but as Sheet1 will be an expanding file, it might not be the best solution needing to change the range references every time ?

Just make the range big enough so you never have to change it?

 

[Bravo2003]

Just make the range big enough so you never have to change it?

That's an option.. I might now the max row count the sheet will grow to soon, so could adjust it then

On a similar need, can I use this with COUNTIF?
so SheetX B4 need to =COUNTIF(Sheet1!N:N,5)
then in B5 COUNTIF the 10 columns along (X:X), etc..

 

[Peter_SSs]

so SheetX B4 need to =COUNTIF(Sheet1!N:N,5)

You now want to check the whole of column N, not just a block of 6 cells again?

If it is not the small block of cells, can we at least put some sort of cap on the rows being checked so we don't have to check all 1 million+ rows?

 

[Bravo2003]

You now want to check the whole of column N, not just a block of 6 cells again?

If it is not the small block of cells, can we at least put some sort of cap on the rows being checked so we don't have to check all 1 million+ rows?

I have worked out I would probably only use probably 365-500 rows max - so that should help to start with as N1:N365

 

[Peter_SSs]

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)