Google Sheets – Sum Cells Until a Certain Value is Reached

formulasgoogle sheets

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?

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 replace 100 with a cell address that contains a target value you enter.

Related Solutions

Google Sheets – How to Sum Cells Until a Specific Value is Matched

Yes, this can be done.

Following I will show an approach that use the running total formula posted by AdamL but first, it prepares the input data to be used by the referred formula:

First sort the input range in inverse order
- C1: =SORT({{A3:B7},ROW(A3:A7)},3,FALSE)
Calculate the running sum for the first (key) column
- F1: =ArrayFormula(SUMIF(ROW(C3:C7),"<="&ROW(C3:C7),C3:C7))
Calculate the running sum for the second (value) column
- G1: =ArrayFormula(SUMIF(ROW(D3:D7),"<="&ROW(D3:D7),D3:D7))
Find and return the output
- H1: =VLOOKUP(A1,F3:G7,2)

Following there is another approach. I made a slight change to the AdamL's formula (replace <= by >):

Calculate the running sum for the first (key) column
- C1: =ArrayFormula(SUMIF(ROW(A3:A7),">"&ROW(A3:A7),A3:A7))
Calculate the running sum for the second (value) column
- D1: =ArrayFormula(SUMIF(ROW(B3:B7),">"&ROW(B3:B7),B3:B7))
Find and return the output
- E1: =VLOOKUP(A1,A3:B7,2,FALSE)

Demo file

Google-sheets – Sum certain numbers in a range of cells that contain text and numbers

Guarding your regexextract() with a regexmatch() should help:

=ARRAYFORMULA(
  IF(REGEXMATCH(P4:ZZ4, "(\d+)"),
     SUM(--REGEXEXTRACT(P4:ZZ4,"(\d+)")),
     ""
    )
  )

If it doesn't, please provide a link to an example spreadsheet.

Best Answer

Related Solutions

Google Sheets – How to Sum Cells Until a Specific Value is Matched

Google-sheets – Sum certain numbers in a range of cells that contain text and numbers

Related Topic