0

I have multiple sheets with three columns each: Date, Invested Amt, and Return Amt. I am trying to merge the values and get a Result dataset to create a single stacked line chart of Invested vs Return against Date.

Update: Demo sheet is available on, https://docs.google.com/spreadsheets/d/1mEQXW8202UsYkVzRVvZEbFrjzioDTB7KFDKQNy4zgEo/edit?usp=sharing

So my questions are,

  1. How to combine these values from multiple sheets to create a stacked line chart while taking care of the missing data?
  2. How to keep the final chart updated automatically when new rows are added the sheets?
2
1

Use an { array expression } to stack the data, and a query() to aggregate it by date, like this:

=arrayformula( query( 
  { 
    { text(Sheet1!A1:A, "yyyy-mm"); text(Sheet2!A2:A, "yyyy-mm"); text(Sheet3!A2:A, "yyyy-mm") }, 
    { Sheet1!B1:C; Sheet2!B2:C; Sheet3!B2:C } 
  }, 
  "select Col1, sum(Col2), sum(Col3) 
   where Col2 is not null 
   group by Col1 
   label Col1 'Month' ", 
  1 
) )

The arrayformula() is required because of the conversion of dates to months with text().

6
  • There are no duplicated rows. With your formula I am unable to get actual sum of values e.g., in Result tab for 15 Dec 2020, the row contains values only from Sheet instead of adding values from Sheet2 01 Dec 2020. – Borderless.Nomad Jun 16 at 11:36
  • I think the formula shows accurate totals across sheets 1 to 3 for each individual date. It is unclear whether you want to aggregate across dates as well. Please insert a new sheet and show your hand-entered expected results there, and explain why they are the correct results. – doubleunary Jun 16 at 15:39
  • I have added a new sheet called "Results - Expected". The results are correct because the values are aggregate across a month. – Borderless.Nomad Jun 19 at 14:56
  • Edited the formula to aggregate the data monthly rather than daily. – doubleunary Jun 20 at 12:00
  • 1
    It is unclear why the last closing value from previous month should be included in the next month. I think that is a different question. Please post it as a new question, and show your hand-entered expected results in a sample spreadsheet when you post. – doubleunary Jul 2 at 14:43

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.