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?
Google Sheets – Sum Cells Until a Certain Value is Reached
formulasgoogle sheets
Related Topic
- Google-sheets – Dividing one column by another based on values on other columns and getting the sum
- Google-sheets – Add a new row to a spreadsheet, copying down formulas when a certain cell reaches a particular value
- Google Sheets – Apply Formula to Each Cell in Column Before Summing
- Google-sheets – Google sheets: summing cells in a column where the cell above contains a string
- Google Sheets – Summing Cells Based on Value in Another Column
- Google Sheets – List Values if Date Matches Cell D1
Best Answer
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 replace100
with a cell address that contains a target value you enter.