Sum/Offset with a dynamic start point

[Peterw_2506]

The problem;

This formula works =SUM(OFFSET(F9,1,-3,10,1)) but the starting point [F9] in this case is not dynamic and is required to become.

Additionally, =LOOKUP(2,1/(F:F=0),C:C) will find the row the last time zero appears in column F (the starting point in this case [F9] to the equation).

These two following formulas are what I’ve tried to do to make [F9] dynamic, but alas both don’t work.
=sum(offset(LOOKUP(2,1/(F:F=0),C:C),1,-3,10,1)
=SUM(OFFSET(INDIRECT(LOOKUP(2,1/(F:F=0),C:C),TRUE),1,-3,10,1))

(The attached image is from cells B2:F13)

Any guidance that can be given in resolving this problem I say thank you in advance.

 

[shift-del]

Hi

=LET(
a,LOOKUP(2,1/(F1:F22=0),ROW(C2:C23)),
b,SUM(INDEX(C:C,a):INDEX(C:C,a+9)),
b)

 

[jasonb75]

Which version of excel are you using?
This should work if I've broken down your existing formula correctly, but there may be more efficient ways to do it.

=SUM(OFFSET(INDIRECT("C"&MATCH(2,1/(F:F=0)/(F:F<>""))),1,0,10,1))

That said, the best way to make it more efficient would be to limit the range where you look for zero. If you only have 1000 rows of data then use $F$1:$F$2000 instead of F:F to improve efficiency by around 99.8%

 

[Peterw_2506]

Which version of excel are you using?
This should work if I've broken down your existing formula correctly, but there may be more efficient ways to do it.

=SUM(OFFSET(INDIRECT("C"&MATCH(2,1/(F:F=0))),1,0,10,1))

Hi Jason, Thank you for your help.
I'm using subscription 365.
Unfortunately, =SUM(OFFSET(INDIRECT("C"&MATCH(2,1/(F:F=0))),1,0,10,1)) still comes up with a #REF error

 

[jasonb75]

Hi, Peter. If you refresh the page you will see an edit to my post to correct something that I missed initially. By using (F:F<>0) it would see the last empty cell as being 0 value so we need to find the last 0 value that is not empty for it to work.

 

[jasonb75]

With office 365 this should be significantly more efficient.

=SUM(INDEX(C:C,SEQUENCE(10,,XMATCH(0,F:F,0,-1)+1)))

 

[Peterw_2506]

With office 365 this should be significantly more efficient.

=SUM(INDEX(C:C,SEQUENCE(10,,XMATCH(0,F:F,0,-1)+1)))

And Bingo! that works. Thank you my friend. I've been scratching my head for 3 days over this problem.