0

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?

0

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.

1
  • Whoa! I really don't understand how this formula works, but it works like a charm! Thanks a lot. Aug 30 at 7:09
0

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
}

enter image description here

1
  • TypeError: Cannot read property 'map' of undefined Am I doing something wrong? Aug 30 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.