Sum each row until value is eached

[mobomojo]

I have a table like in the attached and I am looking to add a formula that can sum up the value column one row at a time, and if the total equals the target value, then return the date on that row.

In the example sheet I've added the date it should return

Does anyone know how to go about this?

Thanks

 

[Chihiro]

If you have access to Lambda function, you can write iterative formula that would handle this.

If not, then other options are...
1. Use Solver tool.
2. Manual calculation using row context increase.
3. VBA/UDF

Using 2nd method.
In D3:
=SUM(C$3:C3)

Copy down.

Then in E2:
=INDEX($B$3:$B$10,MATCH(0,$D$3:$D$10,0))

 

[Peter Bartholomew]

The problem of creating running totals (accumulations / corkscrews) is currently an outstanding problem of dynamic arrays. Smaller problems can be addressed by using MMULT or SUMIFS. In this case, the accumulation can be created using

= LET(
   runningTotal, SUMIFS(Value, Date, "<="&Date),
   XLOOKUP(0, runningTotal, Date) )

to test for an exact match to 0, or

= LET(
   runningTotal, SUMIFS(Value, Date, "<="&Date),
   XLOOKUP(FALSE, runningTotal>0, Date) )

for the first non-positive value.
I also have Charles William's FastExcel function ACCUMULATE which allows

= XLOOKUP(0, ACCUMULATE(Value), Date)

If you wish to blow your mind with Lambda functions you could always try
Ways of performing Accumulation with Dynamic Arrays - Microsoft Tech Community
It certainly was a nightmare developing the formula