Sum of Sum if formula

[gt213]

I'm struggling to make the following formula dynamic and be able to change the cells if references.

I have this formula which works fine:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets!$C$1:$AM$1&"'!A:A"),$A4,INDIRECT("'"&Sheets!$C$1:$AM$1&"'!"&C$1)))

However in sheet 'Sheets' I may not always want to look up $AM$1. So I have tried changing the formula to use Counta and Address to make the reference dynamic, however when I try the following I get #REF!

=SUMPRODUCT(SUMIF(INDIRECT("'"&"Sheets!$C$1:"&ADDRESS(1,COUNTA(Sheets!1:1))&"'!A:A"),$A4,INDIRECT("'"&Sheets!$C$1:$AM$1&"'!"&D$1)))

As a check I also tried typing Sheets!$C$1:$AM$1 in cell O4 and then the following formula which also gave a #REF! error:

=SUMPRODUCT(SUMIF(INDIRECT("'"&O4&"'!A:A"),$A4,INDIRECT("'"&Sheets!$C$1:$AM$1&"'!"&E$1)))

The fact that this last version doesn't work makes me think that it isn't possible, but would love to be proved wrong on this...

 

[FDibbins]

1st try and avoid using full column ranges in SP, it will slow your file down.

2nd, you don't need to use $ for ranges inside INDIRECT, they are text anyway, so wont change.

3rd, try to just add another reference that will contain the range you want (instead of M1). So perhaps...
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets!C1:"&$AA$1&"'!A:A"),$A4,INDIRECT("'"&Sheets!C1:"&$AA$1&"'!"&C$1)))

 

[gt213]

Thanks for the response. That doesn't work, Sheets!$C$1:$AM$1 is a list of all the sheet names in the workbook and if there is anything in there that isn't a sheet name, or the reference goes past the last entry (AM1) if gives an error.