Sumproduct function multiple sheets

J

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

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
 
S

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:D$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:D$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:D$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:D$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:D$25)

My choice would be to write a custom VBA function.
 
A

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
 

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