Sumproduct function multiple sheets

[Junaid]

I have one workbook with several sheets, i want to apply the sumproduct
function.
my file contains 4 week sheets & 1 monthly summary sheet on summary sheet i
want the sum of each product.

A B C D E
1 Name Size Brand Planned Produced
2 XXX 125x24 AAA 10000 8000
3 YYY 125x24 AAA 5000 4500
4 XXX 250x24 BBB 10000 9000

I want to add the planned & Produced columns if the criteria is matched.
range for all sheets is same 25 rows in each sheet.

 

[Bernard Liengme]

Sumproduct cannot be used in 3D but there is a workaround
Let rows 1 and 2 of the summary sheet look like this
Name Size Brand Planned Produced
XXX 125x24 AAA


On each of the weekly sheets use formals such as: =Summary!A2 and
=Summary!B2
Use these values in each weekly sheet to get the SUMPRODUCT value
Let's say the sumproduct for Planned is in G2 on each sheet
Then on the Summary sheet in D2 use =SUM(Week1:Week4!G2)
best wishes

 

[Shane Devenshire]

Hi,

I would use Bernard's approach, but if you don't want those extra columns in
the other sheets:

Since you only have 4 sheets you can copy and paste the function:

=SUMPRODUCT(--($A2=Sheet2!$A$2:$A$25),--(Summary!$B2=Sheet2!$B$2:$B$25),--(Summary!$C2=Sheet2!$C$2:$C$25),Sheet2!D$2$25)

into one cell 4 times with a + between each and then change the sheet names
accordingly to give something like this:

=SUMPRODUCT(--($A2=Sheet2!$A$2:$A$25),--(Summary!$B2=Sheet2!$B$2:$B$25),--(Summary!$C2=Sheet2!$C$2:$C$25),Sheet2!D$2$25)+SUMPRODUCT(--($A2=Sheet3!$A$2:$A$25),--(Summary!$B2=Sheet3!$B$2:$B$25),--(Summary!$C2=Sheet3!$C$2:$C$25),Sheet3!D$2$25)+SUMPRODUCT(--($A2=Sheet4!$A$2:$A$25),--(Summary!$B2=Sheet4!$B$2:$B$25),--(Summary!$C2=Sheet4!$C$2:$C$25),Sheet4!D$2$25)+SUMPRODUCT(--($A2=Sheet5!$A$2:$A$25),--(Summary!$B2=Sheet5!$B$2:$B$25),--(Summary!$C2=Sheet5!$C$2:$C$25),Sheet5!D$2$25)

My choice would be to write a custom VBA function.

 

[T. Valko]

It's possible but complicated trying to use SUMPRODUCT across multiple
sheets (3d referencing).

There is an example formula here (Google Groups):

http://tinyurl.com/d8apqk

 

[Ashish Mathur]

Hi,

I have assumed that the range on every sheet is C3:G6 (including the
headers)

In a H4 of each sheet to be consolidated, type C4&D4&E4 and copy down. Do
the same on the consolidated sheet as well (say this is column H of the
consol sheet). In the consolidated sheet, type the tab name of each sheet,
say A,B,C in range K4:K6.

Now use the following formula for summing the Planned column

=SUMPRODUCT(SUMIF(INDIRECT($K$4:$K$6&"!$H$4:$H$6"),$H4,INDIRECT($K$4:$K$6&"!$F$4:$F$6")))

Use the following formula for summing the produced column

=SUMPRODUCT(SUMIF(INDIRECT($K$4:$K$6&"!$H$4:$H$6"),$H4,INDIRECT($K$4:$K$6&"!$G$4:$G$6")))

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com