SUMPRODUCT, IF and INDIRECT

[thbutterflycollector_]

Hi, I have a SUMPRODUCT and IF formula which works fine together but I am trying to merge this with an INDIRECT formula:

=SUMPRODUCT(IF('No. 1'!$A$1:$A$500=E$4,'No.1'$E$1:$E$500*'No.1'$F$1:$F$500))

No.1 should be the contents of Cell A8, A9 etc.

I've tried:

=SUMPRODUCT(IF(INDIRECT$A8&"!$A$1:$A$500")=E$4,(INDIRECT$A8&"!$E$1:$E$500)*(INDIRECT$A8&"!$F$1:$F$500)),0)

But to no avail. Any help appreciated

 

[DanteAmor]

Try:

=SUMPRODUCT(IF(INDIRECT("'" & $A8 & "'!$A$1:$A$500")=E$4,INDIRECT("'" & $A8 & "'!$E$1:$E$500")*INDIRECT("'" & $A8 & "'!$F$1:$F$500")))

 

[thbutterflycollector_]

Try:

=SUMPRODUCT(IF(INDIRECT("'" & $A8 & "'!$A$1:$A$500")=E$4,INDIRECT("'" & $A8 & "'!$E$1:$E$500")*INDIRECT("'" & $A8 & "'!$F$1:$F$500")))

Thank you.