SUMPRODUCT across several sheets

[Sonny]

I have a SUMPRODUCT function that works nice on a spreadsheet. Now I want to
add several more sheets in the SUMPRODUCT function, without adding a hole new
SUMPRODUCT function for each sheet. The structure is the same in each sheet.

How do I reference several sheets in the same SUMPRODUCT function?

Thanks in advance!

 

[Bernard Liengme]

It is hard to use a 3D reference with SUMPRODUCT
See T. Valko's (aka Biff) message at
http://groups.google.com/group/micr.../browse_thread/thread/0d5b98dff14e43c6?hl=en#

Why not enter a SUMPRODUCT on each sheet an SUM these on the summary sheet
Since the sheets all have the same structure, the simple way to have a
SUMPRODUCT formula on all sheets would be to group these sheets, type the
formula and ungroup.
best wishes

 

[Don Guillett]

A SUMIF across sheets that you may be able to modify
Defined name ms
={"Sheet1","Sheet2","Sheet3"}
ms could be a list on the sheet if preferred

=SUMPRODUCT(SUMIF(INDIRECT(ms&"!$j1:j21"),"xx",INDIRECT(ms&"!k1:k21")))

 

[Jacob Skaria]

If you are looking for a single criteria SUMIF() you can try out the below
with your criteria in current sheet C2 ..and J1:J3 has the sheetnames you
want to look into. Make sure you dont have any blank entries in J1:J3..

=SUMPRODUCT(SUMIF(INDIRECT("'"& J1:J3 &"'!A:A"),C2,INDIRECT("'"& J1:J3
&"'!B:B")))

If this post helps click Yes

 

[Shane Devenshire]

What exactly are you trying to do. More detail might help us give you a
specific answer, because sumproduct itself does not support 3D references.

 

[Sonny]

Ok, I went for your workaround - summarizing on each sheet and then
summmarize that again. But there ought to be an easy way to use SUMPRODUCT.

Thanks!