Sumproduct function multiple sheets

Discussion in 'Microsoft Excel Worksheet Functions' started by Junaid, Mar 22, 2009.

1. JunaidGuest

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.

Junaid, Mar 22, 2009

2. Bernard LiengmeGuest

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" <> wrote in message
news:...
>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, Mar 22, 2009

3. Shane DevenshireGuest

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.

Shane Devenshire, Mar 22, 2009
4. T. ValkoGuest

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" <> wrote in message
news:...
>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, Mar 22, 2009
5. Ashish MathurGuest

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" <> wrote in message
news:...
> 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, Mar 23, 2009