# Combine data from multiple sheets to create a stacked line chart

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`.

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?
• Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Commented Jun 2, 2021 at 7:28

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()`.

• 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. Commented Jun 16, 2021 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. Commented Jun 16, 2021 at 15:39
• I have added a new sheet called "Results - Expected". The results are correct because the values are aggregate across a month. Commented Jun 19, 2021 at 14:56
• Edited the formula to aggregate the data monthly rather than daily. Commented Jun 20, 2021 at 12:00
• 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. Commented Jul 2, 2021 at 14:43