PC Review


Reply
Thread Tools Rate Thread

Sumproduct function multiple sheets

 
 
Junaid
Guest
Posts: n/a
 
      22nd Mar 2009
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.
 
Reply With Quote
 
 
 
 
Bernard Liengme
Guest
Posts: n/a
 
      22nd Mar 2009
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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Junaid" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.



 
Reply With Quote
 
 
 
 
Shane Devenshire
Guest
Posts: n/a
 
      22nd Mar 2009
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.

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

Cheers,
Shane Devenshire


"Junaid" wrote:

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

 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      22nd Mar 2009
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

--
Biff
Microsoft Excel MVP


"Junaid" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.



 
Reply With Quote
 
Ashish Mathur
Guest
Posts: n/a
 
      23rd Mar 2009
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

"Junaid" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE gholly Microsoft Excel Misc 2 28th Sep 2009 05:07 PM
SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter Greg in CO Microsoft Excel Worksheet Functions 0 18th Sep 2008 05:51 PM
Print sheets by "All Sheets in workbook, EXCEPT for specific named sheets". Possible? Corey Microsoft Excel Programming 2 11th Dec 2006 02:35 AM
Sumproduct function used between sheets JoelIgnall Microsoft Excel Worksheet Functions 1 7th Apr 2004 11:36 PM
Help with copying data from multiple sheets to multiple sheets Randy Reese Microsoft Excel Programming 1 18th Mar 2004 11:02 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:06 AM.