+ Reply to Thread
Results 1 to 7 of 7

How to put multiple Sumproduct formulas in one cell

  1. 12-29-2011, 05:24 PM #1
    jimstrongy
    jimstrongy is offline
    Forum Contributor
    Join Date
    11-25-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    107

    How to put multiple Sumproduct formulas in one cell

    I have this 5 formulas the question is how do I put them together in one cell as one formula???


    =SUMPRODUCT(--(US!$A$10:$A$5079=$D2),--(US!$F$10:$F$5079=$I2),--(US!$B$7=$A2)*(US!$H$10:$H$5079))
    *
    =SUMPRODUCT(--(AE!$A$10:$A$5079=$D72),--(AE!$F$10:$F$5079=$I72),--(AE!$B$7=$A72)*(AE!$H$10:$H$5079))
    *
    =SUMPRODUCT(--(AI!$A$10:$A$5079=$D138),--(AI!$F$10:$F$5079=$I138),--(AI!$B$7=$A138)*(AI!$H$10:$H$5079))
    *
    =SUMPRODUCT(--(PL!$A$10:$A$5079=$D204),--(PL!$F$10:$F$5079=$I204),--(PL!$B$7=$A204)*(PL!$H$10:$H$5079))
    *
    =SUMPRODUCT(--(DL!$A$10:$A$5079=$D245),--(DL!$F$10:$F$5079=$I245),--(DL!$B$7=$A245)*(DL!$H$10:$H$5079))
    Reply With QuoteRegister To Reply
  2. 12-29-2011, 05:51 PM #2
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: How to put multiple Sumproduct formulas in one cell

    Try this,

    =SUMPRODUCT(SUMIFS(INDIRECT("'"&{"AI";"US";"AE";"PL";"DL"}&"'!H10:H5079"),INDIRECT("'"&{"AI";"US";"AE";"PL";"DL"}&"'!A10:A5079"),CHOOSE({1,2,3,4,5},$D2,$D72,$D138,$D204,$D245),INDIRECT("'"&{"AI";"US";"AE";"PL";"DL"}&"'!F10:F5079"),CHOOSE({1,2,3,4,5},$I2,$I72,$I138,$I204,$I245)))
    Regards,
    Haseeb Avarakkan
    __________________________________
    "Feedback is the breakfast of champions"
    Reply With QuoteRegister To Reply
  3. 12-29-2011, 06:18 PM #3
    jimstrongy
    jimstrongy is offline
    Forum Contributor
    Join Date
    11-25-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: How to put multiple Sumproduct formulas in one cell

    I tried that and its not working I have to say that it worked for some rows not all
    I'm posting a new formula because the ranges should be same but I was changing same formula in same column so it looked like that ranges were different.

    =SUMPRODUCT(--(US!$A$10:$A$5079=$D2),--(US!$F$10:$F$5079=$I2),--(US!$B$7=$A2)*(US!$H$10:$H$5079))

    =SUMPRODUCT(--(AE!$A$10:$A$5079=$D2),--(AE!$F$10:$F$5079=$I2),--(AE!$B$7=$A2)*(AE!$H$10:$H$5079))

    =SUMPRODUCT(--(AI!$A$10:$A$5079=$D2),--(AI!$F$10:$F$5079=$I2),--(AI!$B$7=$A2)*(AI!$H$10:$H$5079))

    =SUMPRODUCT(--(PL!$A$10:$A$5079=$D2),--(PL!$F$10:$F$5079=$I2),--(PL!$B$7=$A2)*(PL!$H$10:$H$5079))

    =SUMPRODUCT(--(DL!$A$10:$A$5079=$D2),--(DL!$F$10:$F$5079=$I2),--(DL!$B$7=$A2)*(DL!$H$10:$H$5079))
    Reply With QuoteRegister To Reply
  4. 12-29-2011, 06:23 PM #4
    jimstrongy
    jimstrongy is offline
    Forum Contributor
    Join Date
    11-25-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: How to put multiple Sumproduct formulas in one cell

    I tried that and its not working I have to say that it worked for some rows not all
    I'm posting a new formula because the ranges should be same but I was changing same formula in same column so it looked like that ranges were different.

    =SUMPRODUCT(--(US!$A$10:$A$5079=$D2),--(US!$F$10:$F$5079=$I2),--(US!$B$7=$A2)*(US!$H$10:$H$5079))

    =SUMPRODUCT(--(AE!$A$10:$A$5079=$D2),--(AE!$F$10:$F$5079=$I2),--(AE!$B$7=$A2)*(AE!$H$10:$H$5079))

    =SUMPRODUCT(--(AI!$A$10:$A$5079=$D2),--(AI!$F$10:$F$5079=$I2),--(AI!$B$7=$A2)*(AI!$H$10:$H$5079))

    =SUMPRODUCT(--(PL!$A$10:$A$5079=$D2),--(PL!$F$10:$F$5079=$I2),--(PL!$B$7=$A2)*(PL!$H$10:$H$5079))

    =SUMPRODUCT(--(DL!$A$10:$A$5079=$D2),--(DL!$F$10:$F$5079=$I2),--(DL!$B$7=$A2)*(DL!$H$10:$H$5079))
    Reply With QuoteRegister To Reply
  5. 12-29-2011, 06:28 PM #5
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: How to put multiple Sumproduct formulas in one cell

    Try this,

    =SUMPRODUCT(SUMIFS(INDIRECT("'"&{"AI";"US";"AE";"PL";"DL"}&"'!H10:H5079"),INDIRECT("'"&{"AI";"US";"AE";"PL";"DL"}&"'!A10:A5079"),$D2,INDIRECT("'"&{"AI";"US";"AE";"PL";"DL"}&"'!F10:F5079"),$I2))
    Reply With QuoteRegister To Reply
  6. 12-30-2011, 10:55 AM #6
    jimstrongy
    jimstrongy is offline
    Forum Contributor
    Join Date
    11-25-2011
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    107

    Re: How to put multiple Sumproduct formulas in one cell

    Unfortunately that is not working either.
    Reply With QuoteRegister To Reply
  7. 12-30-2011, 01:50 PM #7
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: How to put multiple Sumproduct formulas in one cell

    Can you please attach your sample file?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1