SUMPRODUCT across worksheets

NJActuary

New Member
Joined
Aug 4, 2003
Messages
32
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,299
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,803
Messages
5,524,970
Members
409,612
Latest member
Jagadeeswaran Stalin

This Week's Hot Topics

Top