1

I'd like to join by date on these 2 sheets (Income & Expense), on key values month and day and add constant value from FixedCost sheet,

Income: enter image description here

Expense: enter image description here

Add FixedCost to every row/date:

enter image description here

Desired result, with subtotal on Dashboard sheet: enter image description here

Example sheet: https://docs.google.com/spreadsheets/d/1O1rZUstDNSXPdUVXvaDfPO4rAQs2cJWHimfGxbddtNU/edit#gid=1037187610

How can this be done? I've seen the array formula solution, but it looks like an inner join instead of a left join here: LEFT JOIN two and more tables in Google Sheets

1

=ARRAYFORMULA({Income!A1:E1, Expense!C1:D1, "FixedCost", "SubTotal"; 
 INDIRECT("Income!A2:E"&COUNTA(Income!A2:A)+1),
 IFERROR(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(INDIRECT("Income!A2:B"&
 COUNTA(Income!A2:A)+1)),,999^99)), 
 {TRANSPOSE(QUERY(TRANSPOSE(Expense!A2:B),,999^99)), Expense!C2:D}, {2, 3}, 0), 0),
 TRANSPOSE(SPLIT(REPT(FixedCost!A2&"♠", COUNTA(Income!A2:A)), "♠")),
 ARRAY_CONSTRAIN(Income!C2:C-IFERROR(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(
 INDIRECT("Income!A2:B"&COUNTA(Income!A2:A)+1)),,999^99)), 
 {TRANSPOSE(QUERY(TRANSPOSE(Expense!A2:B),,999^99)), Expense!C2:D}, 2, 0), 0)-
 FixedCost!A2, COUNTA(Income!A2:A), 1)})

0

  • May I ask, without FixedCost & SubTotal, what it would look like? I'm thinking adding those 2 requirements is making it overly complex, I can add those fields separate from the ArrayFormula.. I'm trying to figure out the query but it's tough wrapping my head around it – sojim2 May 28 at 12:41
  • 1
    it would look like this: =ARRAYFORMULA({Income!A1:E1, Expense!C1:D1; INDIRECT("Income!A2:E"&COUNTA(Income!A2:A)+1), IFERROR(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(INDIRECT("Income!A2:B"& COUNTA(Income!A2:A)+1)),,999^99)), {TRANSPOSE(QUERY(TRANSPOSE(Expense!A2:B),,999^99)), Expense!C2:D}, {2, 3}, 0), 0)}) – user0 May 28 at 12:45

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.