SUMPRODUCT across worksheets

[NJActuary]

I have a workbook with many worksheets (let's call them SheetA, SheetB, ..., SheetZ), each formatted identically. I need to do a sumproduct of cell E1 with E2 across all the worksheets. I tried something of the form
=SUMPRODUCT('SheetA:SheetZ'!E1,'SheetA:SheetZ'!E2)
but that gave me a #REF! error.

Any ideas on how to correct my syntax to get this right?

Thanks.

 

[Domenic]

Let A2:A27 contain the sheet names, then try...

=SUMPRODUCT(N(INDIRECT("'"&$A$2:$A$27&"'!E1")),N(INDIRECT("'"&$A$2:$A$27&"'!E2")))

If the references for E1 and E2 need to be relative, try the followoing formula instead...

=SUMPRODUCT(N(INDIRECT("'"&$A$2:$A$27&"'!"&CELL("address",E1))),N(INDIRECT("'"&$A$2:$A$27&"'!"&CELL("address",E2))))

Hope this helps!