SUMPRODUCT across several sheets

S

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

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")))
 
J

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
 
S

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

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top