Adding up cells in a column till the value of sum reaches a certain value - Google Sheets

Question

I have a column with a list of numbers. I want to calculate the sum of the numbers, starting from the top until the value of sum reaches a fixed value, and then stop and return the cell address of the cell where the sum reached its limit. Is there a formula for this? If not, what would be the script for a custom formula?

Answer 1

Suppose your original numbers are in A2:A. This would return the cell address where the cumulative sum reached or exceeded 100:

=ArrayFormula(ADDRESS(VLOOKUP(TRUE,{SUMIF(ROW(A2:A),"<="&ROW(A2:A),A2:A)>=100,ROW(A2:A)},2,FALSE),COLUMN(A2)))

You can change the 100 to whatever target you like; or you can replace 100 with a cell address that contains a target value you enter.

Answer 2

Add the custom function below and use it =ADDRESS(findsumindex(A2:A,622)+row($A$2),1):

function findsumindex(range, targetSum){
  const values=range.map(row=>row[0])
  let sum=0
  const index = values.findIndex((value,i)=>{
    sum += value
    return sum>=targetSum
  })
  return index
}