Consolidated P&L Multiple Tabs - Sumif Index Match

[Astros1243]

Hi all,

I have an exercise where I am trying to create a Consolidate P&L between 5 companies in a "Consolidated P&L" tab. This consolidated P&L is looking to pull in the sums from multiple tabs where the row lines are exactly the same, however the dates may not exactly match up in the same columns in each of them (the companies would open at different dates).

I would do a multiple sum if or a consolidated pivot however, the exercise is looking to be dynamic when a new tab is opened to automatically add the new tab in the formula. I also tried doing a sum if index match however it returns a #Value (I am assuming due to it going across multiple sheets because it works when I only include Company1! in the formula but not Company1:Company5!.

Is anybody aware of a formula I can use that can sum up these amounts from each tab matching to the corresponding date in the column header?.

Thank you,
Astro.

 

[helenshas]

Hi all,

I have an exercise where I am trying to create a Consolidate P&L between 5 companies in a "Consolidated P&L" tab. This consolidated P&L is looking to pull in the sums from multiple tabs where the row lines are exactly the same, however the dates may not exactly match up in the same columns in each of them (the companies would open at different dates).

I would do a multiple sum if or a consolidated pivot however, the exercise is looking to be dynamic when a new tab is opened to automatically add the new tab in the formula. I also tried doing a sum if index match however it returns a #Value (I am assuming due to it going across multiple sheets because it works when I only include Company1! in the formula but not Company1:Company5!.

Is anybody aware of a formula I can use that can sum up these amounts from each tab matching to the corresponding date in the column header?.

Thank you,
Astro.

Hi Did you manage to get an fix for this, as I am trying to do the same but with different titles