Sumproduct function multiple sheets

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

  1. Junaid

    Junaid Guest

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

  2. 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
    #2
    1. Advertisements

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

    --
    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
    #3
  4. Junaid

    T. Valko Guest

    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
    #4
  5. 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" <> 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
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. JoelIgnall

    Sumproduct function used between sheets

    JoelIgnall, Apr 7, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    175
    Frank Kabel
    Apr 7, 2004
  2. Guest

    sumproduct from multiple sheets

    Guest, Aug 3, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    259
    RagDyer
    Aug 3, 2005
  3. Guest

    sumproduct looking at multiple sheets

    Guest, Feb 27, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    299
    Domenic
    Feb 27, 2006
  4. sumproduct for multiple sheets

    , Jan 25, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    223
    Bob Phillips
    Jan 25, 2007
  5. Greg in CO

    SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter

    Greg in CO, Sep 18, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    582
    Greg in CO
    Sep 18, 2008
Loading...

Share This Page