Google Sheets – Update Cell Based on Matching Fields in Separate Tabs

google sheetsworksheet-function

I'm trying to update a cell in Google Spreadsheets where its value comes from another sheet and based on the date range entered with it.

Here is what the two worksheets look like in the file.

Debtor Payments Sheet

January
Name | Amount Recieved | Date Recieved
Joe     $2.00            1/20/2011  
Kim     $1.00            1/25/2011                   


November
Name | Amount Recieved | Date Recieved
Joe     $6.00            2/11/2011

Debtor Payment By Month Sheet

Name | Jan   | Feb
Joe    $2.00   $6.00
Kim    $1.00

What I want to happen is:

When a payment is made in a given month in "Debtor Payments Sheet".
Add the amount to "Debtor Payment By Month Sheet" in the column for that month where the name matches.

With these conditions:

the name will only show up once in Pay by Month
New names will be added to "Debtor Payments" and need to be put manually or otherwise into "Debtor Payment By Month Sheet"

Best Answer

Basically, you need to use a few formulas to accomplish this, like:

UNIQUE
FILTER
SUM

Walk through the spreadsheet I prepared and see the formula's for yourself: file I prepared with example

Related Solutions

Google Sheets – Nested IF Statement with Same Second Part

Assuming you have "BANK" in I3:I20 (as opposed to "AMEX") for bank payments, then I believe you can achieve your goal by reversing your logic (equal to "BANK", rather than not equal to "AMEX")...

Change the formula in F24 to:

=arrayformula(sumif(H3:H20 & I3:I20,E24:E33 & "BANK",D3:D20))

This will then populate cells F24:F33 with the appropriate values. & is the string concatenation operator, so this effectively compares "1BANK" with "1AMEX" (for instance).

Google Sheets – Arrayformula with Dates Before and After

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.