Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-query

Find Row Where Sum is Reached from Single Joined Column (not a range of cells)

I'm trying to run a formula to identify in which row a total sum is reached.

I've been able to do that calculation when I have an entire range of cells to work with, however, I'm doing a filter / join calculation because I need to do this from an individual row with all the data instead of an entire range of cells.

Here is an example google sheet (EDITABLE - feel free) where you can see the range and working formula (both below). Help getting this from the single-cell versions on the top would be very helpful. The error I get with both row() & index() formulas is that the "argument must be a range".

If there's another way to do this besides the single-cell I had that doesn't require referencing the range (e.g. using FILTER) then I'm open to it.

My desired result is to be able to pull the get the second column (date) at the point when the sum is reached (can be via the INDEX & MATCH formula I used or an alternative). This will tell me the earliest date that feeds into the desired sum.

Solution

  • Yes unfortunately you can't do that trick with SUMIFS to get a running total unless the column being totalled is an actual range.

    The only approach I know is to multiply successive values by a triangular array like this:

    1 0 0 ...
1 1 0 ...
1 1 1 ...

    so you get just the sum of the first value, the first 2 values, then 3 values up to n.

    This is the formula in F5:

    =ArrayFormula(match(E14,mmult(IF(ROW(A1:INDEX(A1:ALL1000,COUNT(split(A5,",")),COUNT(split(A5,","))))>=
COLUMN(A1:INDEX(A1:ALL1000,COUNT(split(A5,",")),COUNT(split(A5,",")))),1,0),TRANSPOSE(SPLIT(A5,",")))))

    And the formula in F6 is just

    =to_date(INDEX(TRANSPOSE(SPLIT(B5,",")),F5,1))

    enter image description here

    EDIT

    You might have guessed that the above formula was adapted from Excel, where you try to avoid volatile functions like Offset and Indirect.

    I have realised since posting this answer that it could be improved in two ways:

    (1) By using Offset or Indirect, thus avoiding the need to define a range of arbitrary size like A1:ALL1000

    (2) By implying a 2D array by comparing a row and column vector, rather than actually defining a 2D array. This would give you something like this in F5:

    =ArrayFormula(match(E14,mmult(IF(ROW(indirect("A1:"&address(COUNT(split(A5,",")),1)))>=
COLUMN(indirect("A1:"&address(1,COUNT(split(A5,","))))),1,0),TRANSPOSE(SPLIT(A5,",")))))

    which could be further simplified to:

    =ArrayFormula(match(E14,mmult(IF(ROW(indirect("A1:A"&COUNT(split(A5,","))))>=
COLUMN(indirect("A1:"&address(1,COUNT(split(A5,","))))),1,0),TRANSPOSE(SPLIT(A5,",")))))