Google Sheets – How to Query Until Reaching a Particular Sum

google sheetsgoogle-sheets-query

I have a sheet with a lot of data, but to keep this simple let's say column A has a product, column B has a date, and column C has an integer which can be blank, 1, or an integer greater than 1:

Product  Date      # Sold
Thing1   2020-9-9       1
Thing2   2020-9-9       2
Thing3   2020-9-9       3
Thing1   2020-8-8       1
Thing2   2020-8-8       2
Thing3   2020-8-8       1
Thing1   2020-7-7       2

I need to find the date on which I sold the 3rd-last of that item:

Product  Turn 3 Date
Thing1   2020-7-7
Thing2   2020-8-8
Thing3   2020-9-9

Note that for other reasons I will likely end up with a Query in each cell of the second table anyway, so it doesn't need to get any more complicated than something like:

=query(Table1!A1:C,"Select min(B) where A='"&A2&"' limit X")

where "X" would be how to limit it to the running total of 3.

Thanks in advance for any ideas!

Best Answer

I did finally solve this.

The solution involved first creating another column with cumulative sales of each product. Using the data above:

Product  Date      # Sold   Cumulative
Thing1   2020-9-9       1   1
Thing2   2020-9-9       2   2
Thing3   2020-9-9       3   3
Thing1   2020-8-8       1   2
Thing2   2020-8-8       2   4
Thing3   2020-8-8       1   4
Thing1   2020-7-7       2   3

The formula for the cumulative column looks like this:

 =sumproduct(--($A$2:$A=$A2)*--($B$2:$B>=$B2),$C$2:$C)

The -- has the effect of converting a boolean to a number. The * becomes a logical AND. The sumproduct essentially multiplies the matrices from its arguments.

So, if something in column A is the same as the thing in cell A2 ($A$2:$A=$A2) AND the date is greater than the date on this row, the first argument is a 1 and it gets multiplied by the number of things sold on that row. But, this calculation is looking at all of the other rows on each and every row, so I can imagine that large data volumes might not work well with it.

Anyway, once we have that Cumulative column, it's a pretty simple query to get the date on which the 3rd-last item was sold:

=QUERY($A$2:$D,"Select B where A='"&A2&"' and D>3 Order By B desc Limit 1")

Works like a charm so far!

Code

function mySplit(range, delimiter) {
  var output = [], maxCol = range[0].length;
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var colCounter=0, counter=0;
    for(var j=0, jLen=range[0].length; j<jLen; j++) {
      if(range[i][j] == "") {
        colCounter++;
      } else {
        if(range[i][j].split(delimiter).length > 1) {
          counter++;
        }
      }
    }
    output.push(counter + "/" + (maxCol - colCounter));
  }  
  return output;  
}

Screenshot

enter image description here

Example

I've created an example file for you: Certain Text Characters
If you copy the file, then you can access the code. Add the code into your existing sheet by going to Tools\Script editor in the main menu. Paste the code and save.

Google Sheets – Running Total with Array Formula

If you did not insist on array-based formula, then

sumifs(C:C, A:A, A2, B:B, "<="&B2)

entered in D2 and copied around the D column, would do the job.

I don't think that an arrayformula-based solution exists, because arrayformula runs through an array once, performing some element-wise operations. You want to compare each element with all others, which is one step further in complexity (a double for loop).

But you can use a custom function, something like =runningtotal(A2:A, B2:B, C2:C) which processes the columns and returns an array of running totals. The script implementing this function is below; add it via Tools > Script Editor. The logic is quite straightforward: as said above, it's a double for loop.

function runningTotal(names, dates, amounts) {
  var sum, totals = [], n = names.length;
  if (dates.length != n || amounts.length != n) {
    return 'Error: need three columns of equal length'; 
  }
  for (var i = 0; i < n; i++) {
    if (names[i][0]) {
      sum = 0;
      for (var j = 0; j < n; j++) {
        if (names[j][0] == names[i][0] && dates[j][0] <= dates[i][0]) {
          sum = sum + amounts[j][0];
        }
      }
    }
    else {
      sum = '';
    }
    totals.push([sum]);
  }
  return totals;
}

Best Answer

Related Solutions

Google Sheets – Counting Cells Containing Certain Text Characters

Code

Screenshot

Example

Google Sheets – Running Total with Array Formula

Related Topic