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/microsoft.public.excel.worksheet.functions/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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Sonny" <So...@discussions.microsoft.com> wrote in message
news:BD1F1480-1C39-475D...@microsoft.com...

[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")))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com

"Sonny" <So...@discussions.microsoft.com> wrote in message
news:BD1F1480-1C39-475D...@microsoft.com...

[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
---------------
Jacob Skaria

[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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

[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!