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

Improve this question

1 Answer 1

Reset to default
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

Improve this answer
2
  • 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, 2019 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, 2019 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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