SUMPRODUCT(SUMIFS(INDIRECT - Multiple Tabs

[tmp123456]

Hi,

I have a collection of cashflows which I am looking to consolidate onto a master sheet (all within the same workbook). Each individual tab has the same account code and date references. I have a name range for the individual tabs but I cannot seem to find the correct syntax to pull the respective values from the underlying tab on the given date.

For example 1.1 Acquisition below should be populated in May 2020 with all the purchases (Cell I8). The formula I currently have is returning a value (zero) but is not returning the correct one.

I'd also like the formula to work if I turn one of the "Active Tabs" off - but that's more of a nice to have

Any help is appreciated!

 

[tmp123456]

I have had some luck with:
=SUM(SUMIFS(INDEX(INDIRECT("'"&Sheets&"'!$A:$FD"),0,MATCH(E$5,INDIRECT("'"&Sheets&"'!$A$5:$FD$5"),0)),INDIRECT("'"&Sheets&"'!$A:$A"),$A8)) - which returns values from the underlying tabs just isn't summing to the correct monthly figure for each code

 

[tmp123456]

I have had some luck with:
=SUM(SUMIFS(INDEX(INDIRECT("'"&Sheets&"'!$A:$FD"),0,MATCH(E$5,INDIRECT("'"&Sheets&"'!$A$5:$FD$5"),0)),INDIRECT("'"&Sheets&"'!$A:$A"),$A8)) - which returns values from the underlying tabs just isn't summing to the correct monthly figure for each code

And just to add that if I change the named range to just one of the input tabs, it returns the correct result. The issue is when I update the named range to add even one more tab the results start going askew