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!

It is hard to use a 3D reference with SUMPRODUCT
See T. Valko's (aka Biff) message at
http://groups.google.com/group/micro...14e43c6?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" wrote in message
...
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!

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",IND IRECT(ms&"!k1:k21")))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sonny" wrote in message
...
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!

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


"Sonny" wrote:

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!

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" wrote:

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!

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!

"Bernard Liengme" wrote:

It is hard to use a 3D reference with SUMPRODUCT
See T. Valko's (aka Biff) message at
http://groups.google.com/group/micro...14e43c6?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" wrote in message
...
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!