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

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.

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.

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.

Ashish Mathur
Guest
Posts: n/a

 23rd Mar 2009
Hi,

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

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.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 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 OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post gholly Microsoft Excel Misc 2 28th Sep 2009 05:07 PM Greg in CO Microsoft Excel Worksheet Functions 0 18th Sep 2008 05:51 PM Corey Microsoft Excel Programming 2 11th Dec 2006 02:35 AM JoelIgnall Microsoft Excel Worksheet Functions 1 7th Apr 2004 11:36 PM Randy Reese Microsoft Excel Programming 1 18th Mar 2004 11:02 PM

Features

Newsgroups

All times are GMT +1. The time now is 04:55 PM.