2

Here is what my spreadsheet looks like

My Chart

I enter recurring weekly payments details in G,H,I,J & K (Name,Amount,Day of week, Start Date, End Date respectively). This arrayformula:

=arrayformula(sumif(I2:I32,C2:C32,H2:H32))

shows the amount due (D), on the Date (A), whose day of the week (C) matches the one defined in I.

Link to example to play with

If I have something that will be due every Friday for example I put the name for the bill in G, the amount in H, and the day of the week # in I. When I do that column D shows amount that is due and repeats it on all the dates where column C (DOW#) match the provided value in column I.

What I would like to do is
make column D not add the amount if the date in A is before the date in column J or after the date in column K. It should work if neither J or K have values, if J has a value, if K has a value or if both J & K have a value.

All my attempts at figuring out how to compare the dates with gt (>) & lt (<) in the same arrayformula have failed.

4
  • Can you share a doc with us Lazfish? And show us the expected result.....sounds boring, but very helpful !! – Jacob Jan Tuinstra Jan 10 '14 at 8:16
  • I added the doc you wanted to see. @JacobJanTuinstra – gooddadmike Jan 12 '14 at 7:58
  • greatly clarified my description in SE here and my expected output was all messed up. It should make sense now. @JacobJanTuinstra – gooddadmike Jan 13 '14 at 4:37
  • I have to wait 24 hours to award this. Awesome! – gooddadmike Jan 18 '14 at 22:19
3
+150

I wrote a custom function in Google Apps Script that takes all the criteria you want it to have (and more) in consideration.

Code

function myPayments(paymentPlan, dateRange) {
  var output = [];  
  for(var i=0, iLen=dateRange.length; i<iLen; i++) {
    var checkDate = new Date(dateRange[i]);
    var weekDay = checkDate.getDay()+1, sum=0;
    for(var j=0, jLen=paymentPlan.length; j<jLen; j++) {
      if(paymentPlan[j][1] == weekDay) {   
        if(paymentPlan[j][2] === "" && paymentPlan[j][3] === "") {
          sum += paymentPlan[j][0];
        } else if(paymentPlan[j][2] === "") {
          if(checkDate <= new Date(paymentPlan[j][3])) {
            sum += paymentPlan[j][0];
          }
        } else if(paymentPlan[j][3] === "") {
          if(checkDate >= new Date(paymentPlan[j][2])) {
            sum += paymentPlan[j][0];
          }
        } else if(paymentPlan[j][2] !== "" && paymentPlan[j][3] !== "") {
          if(checkDate >= new Date(paymentPlan[j][2]) && 
            checkDate <= new Date(paymentPlan[j][3])) {
            sum += paymentPlan[j][0];
          }
        } 
      }
    }
    if(sum == 0) {
      output.push([]);
    } else {
      output.push([sum]);
    }
  } 
  return output;
}

Screenshot

enter image description here

Remarks

If you extend the paymentPlan range, to contain the rest of the data as presented in the PAYMENT PLAN sheet, then you will notice that overlapping payments will be summed.

Add the script under Tools>Script editor and press the save button (in your case it's already there).

Example

I've added the solution in your example file.

1
  • Perfect! I think I will let the bounty end so I can award more. I don't really have experience with this bounty stuff so if you think that won't work I will accept now. – gooddadmike Jan 18 '14 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.