SUM between varying points

[Will S]

Heya,

There is probably an easy solution to this that I'm missing, but I've got a long series of numbers that can change values (Anywhere from 0-9) for example purposes lets say A1:A30, and in the row below I've got values generated from other formulas.

I need to try and find the sum between each cell in A1:A30 that equals 8 so I can then compare it with the number of cells in between the two 8s.

For example, say A1, A6, A15, A27 equal 8, I'd like a formula that can find the sum between A1 and A6, A7 and A15, A16 and A27 etc (where the cells that contain 8s can change). Maybe something with a count that can reset the sum each time it hits an 8 so it can then be compared with the number of cells between A1 and A6, A7 and A15, A16 and A27 etc.

Additional Info: I can't use any add-ons or macros for this.

Any help is greatly appreciated.

 

[MikeWx]

Try the following array formula in Cell B2 (entered with CTL SHIFT ENTER instead of just ENTER), then copy it down. It will sum the rows between and including the current occurrence of 8 back to the prior occurrence of 8.

=IF(A2<>8,"",SUM(OFFSET(A2,SUM(-(ROW(A2)-MAX(IF($A$1:A1=8,ROW($A$1:A1))))),0,SUM(ROW(A2)+1-MAX(IF($A$1:A1=8,ROW($A$1:A1)))))))

 

[Will S]

This is working really well, I've got a few adjustments, since this morning I was told that it had to be in columns rather than rows so that needs to be changed (Simple enough) but as well as this, is it possible to have it so that it could only sum the numbers >9, still making sure it only adds numbers between 8s? I'd assume it just means a SUMIF is used rather than SUM but I can't quite seem to reverse engineer the formula you've used.. yet.

 

[MikeWx]

I set up my test data in Cells A1:AD1 and reoriented the OFFSET statement to work horizontally. Here is the array formula I put in Cell B2. Copy it across horizontally.

=IF(B1<>8,"",SUMIF(OFFSET(B1,0,SUM(-(COLUMN(B1)-MAX(IF($A$1:A1=8,COLUMN($A$1:A1))))),1,SUM(COLUMN(B1)+1-MAX(IF($A$1:A1=8,COLUMN($A$1:A1))))),">9"))

 

[Will S]

Thanks, it's worked perfectly, I've been getting a few issues with circular reference but that's from where I've suited it to the application and I'm managing to work around it. You've been a big help.

 

[MikeWx]

Thanks for your feedback.