Google-sheets – Left join 2 sheets on 2 key values, add constant value

I would write a custom function for this, to be used as

=combinations(table1!B2:B, table1!C2:C, table2!A2:A, table2!C2:C)

that is, the parameters are ValueColumn1, KeyColumn1, ValueColumn2, KeyColumn2.

The logic is entirely straightforward: run a double loop and collect whatever matches.

function combinations(range1, key1, range2, key2) {
  var output = [];
  if (range1.length != key1.length || range2.length != key2.length) {
    throw new Error('Each range must have the same length as its key'); 
  }
  for (var i = 0; i < range1.length; i++) {
    for (var j = 0; j < range2.length; j++) {
      if (key1[i][0] === key2[j][0] && key1[i][0] !== '') {
        output.push([range1[i][0], range2[j][0]]);
      }
    }
  }
  return output;
}

Well, I've solve this, but by creating three additional rows. You can hide them later. You can access that spreadsheet here. First off, you gotta create a row (preferably named Count) that counts each row with unique number assigned to each entry using COUNTIF function. Then an TempDate column which is same as Date column in your Data sheet, but having it right next to Count column will help us later. Then Create a column named UniqueTemp which filters out duplicate entries in Count column. And then a Date column which uses a lookup function from your UniqueTemp column to the Date column. You then just add each Count entry in your Data sheet for every Date using sumif and then you got your work done. Hope this solves your problem.

Also, you could check out the spreadsheet linked here for formulas if I wasn't clear enough.