Sum/Offset with a dynamic start point

Peterw_2506

Board Regular
Joined
Jan 28, 2011
Messages
78
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.
 

Attachments

  • Picture1.png
    Picture1.png
    33.6 KB · Views: 24

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
613
Office Version
  1. 365
Platform
  1. Windows
Hi

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,975
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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%
 
Last edited:

Peterw_2506

Board Regular
Joined
Jan 28, 2011
Messages
78
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.
Excel Formula:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
14,975
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,975
Office Version
  1. 365
Platform
  1. Windows
With office 365 this should be significantly more efficient.
Excel Formula:
=SUM(INDEX(C:C,SEQUENCE(10,,XMATCH(0,F:F,0,-1)+1)))
 

Peterw_2506

Board Regular
Joined
Jan 28, 2011
Messages
78
With office 365 this should be significantly more efficient.
Excel Formula:
=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.
 

Forum statistics

Threads
1,175,486
Messages
5,897,689
Members
434,673
Latest member
kktfc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top