Closed Thread
Results 1 to 9 of 9

SUMPRODUCT is broken (I swear!)

  1. #1
    brandonc
    Guest

    SUMPRODUCT is broken (I swear!)

    Hi all-

    Of course SUMPRODUCT is not broken, but I had to get your attention somehow.

    Hoping you can help with a problem I've been kicking around for a while now.
    This problem is part of a larger capacity planning tool I am creating.

    ** Two tables I have:
    1. Use Case & Interface ID pairs, along with % usage of the interface. A
    single Use Case can access many Interfaces.
    2. Load per Use Case ID.

    ** What I want to calculate:
    1. Load per interface ID. Over all Use Cases for this Interface, sum the
    product of interface usage (%) and Use Case load.

    For this post, I threw all the tables on one sheet to make it easier to talk
    about.

    ** A1:C7 is the Use Case & Interface pairs with interface usage %:
    Column A: {Use Case Id, UC1, UC2, UC2, UC3, UC3, UC3}
    Column B: {Interface Id, Int2, Int1, Int7, Int7, Int5, Int2}
    Column C: {% Interface Usage, 100%, 10%, 90%, 80%, 5%, 15%}

    ** E1:F4 is the load per Use Case
    Column E: {Use Case Id, UC1, UC2, UC3}
    Column F: {Load per Use Case, 38000, 1500, 125}

    ** H1:I5 is the Interface load
    Column H: {Interface ID, Int1, Int2, Int5, Int7}
    Column I: {Load per Interface, <formula I need help with>}


    I did the calculations by hand, and here is the load per interface I would
    expect:
    Int1=150, Int2=38018.75, Int5=6.25, Int7=1450

    I've tried playing around with the usual suspects for a while, SUMPRODUCT,
    SUMIF, & VLOOKUP, but this one has me stumped. Any help is greatly
    appreciated!

    Thanks!
    -brandon


  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    You could use this formula in I2 copied down to I5

    =SUMPRODUCT(--(B$2:B$7=H2),C$2:C$7,LOOKUP(A$2:A$7,E$2:F$4))

    Note because of use of LOOKUP E2:E4 must be ascending order

  3. #3
    brandonc
    Guest

    Re: SUMPRODUCT is broken (I swear!)


    "daddylonglegs" wrote:
    >
    > You could use this formula in I2 copied down to I5
    >
    > =SUMPRODUCT(--(B$2:B$7=H2),C$2:C$7,LOOKUP(A$2:A$7,E$2:F$4))
    >
    > Note because of use of LOOKUP E2:E4 must be ascending order
    >


    Thanks for a solution and quick reply! Unfortunately, I should have
    mentioned I cannot guarantee any column is in ascending order. All of the
    Use Case and Interface IDs are user-entered from other sheets.

    Is there a way to calculate it if it's not in ascending order?

  4. #4
    brandonc
    Guest

    RE: SUMPRODUCT is broken (I swear!)



    "Gary L Brown" wrote:

    > Guess your word of honor isn't very good.
    > Come again soon, NOT.
    >
    > --
    > Gary Brown
    >
    >
    > "brandonc" wrote:
    >
    > > Hi all-
    > >
    > > Of course SUMPRODUCT is not broken, but I had to get your attention somehow.
    > >


    My apologies if I needlessly whipped up the Excel bug-fix team into a
    frenzy. Stand down! Stand down!

    I was actually poking fun at other similar posts I've seen claiming an Excel
    bug must be the only explanation for their quirky results. Only to find out
    they just didn't know how to structure the formula in the first place.

    So Gary, have an answer for me, or just here to shoo me away?

  5. #5
    Domenic
    Guest

    Re: SUMPRODUCT is broken (I swear!)

    Here's another way...

    =SUMPRODUCT(SUMIF(E$2:E$4,A$2:A$7,F$2:F$4),--(B$2:B$7=H2),C$2:C$7)

    ....which doesn't require E2:E4 to be sorted in ascending order.

    Hope this helps!

    In article <2D409B92-6B89-4189-8D64-31B37EFA5C03@microsoft.com>,
    "brandonc" <brandonc@discussions.microsoft.com> wrote:

    > Hi all-
    >
    > Of course SUMPRODUCT is not broken, but I had to get your attention somehow.
    >
    > Hoping you can help with a problem I've been kicking around for a while now.
    > This problem is part of a larger capacity planning tool I am creating.
    >
    > ** Two tables I have:
    > 1. Use Case & Interface ID pairs, along with % usage of the interface. A
    > single Use Case can access many Interfaces.
    > 2. Load per Use Case ID.
    >
    > ** What I want to calculate:
    > 1. Load per interface ID. Over all Use Cases for this Interface, sum the
    > product of interface usage (%) and Use Case load.
    >
    > For this post, I threw all the tables on one sheet to make it easier to talk
    > about.
    >
    > ** A1:C7 is the Use Case & Interface pairs with interface usage %:
    > Column A: {Use Case Id, UC1, UC2, UC2, UC3, UC3, UC3}
    > Column B: {Interface Id, Int2, Int1, Int7, Int7, Int5, Int2}
    > Column C: {% Interface Usage, 100%, 10%, 90%, 80%, 5%, 15%}
    >
    > ** E1:F4 is the load per Use Case
    > Column E: {Use Case Id, UC1, UC2, UC3}
    > Column F: {Load per Use Case, 38000, 1500, 125}
    >
    > ** H1:I5 is the Interface load
    > Column H: {Interface ID, Int1, Int2, Int5, Int7}
    > Column I: {Load per Interface, <formula I need help with>}
    >
    >
    > I did the calculations by hand, and here is the load per interface I would
    > expect:
    > Int1=150, Int2=38018.75, Int5=6.25, Int7=1450
    >
    > I've tried playing around with the usual suspects for a while, SUMPRODUCT,
    > SUMIF, & VLOOKUP, but this one has me stumped. Any help is greatly
    > appreciated!
    >
    > Thanks!
    > -brandon


  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote Originally Posted by Domenic
    Here's another way...

    =SUMPRODUCT(SUMIF(E$2:E$4,A$2:A$7,F$2:F$4),--(B$2:B$7=H2),C$2:C$7)

    ....which doesn't require E2:E4 to be sorted in ascending order.
    ah Domenic, very nice

    all I could think of was to use another column.......

  7. #7
    Gary L Brown
    Guest

    RE: SUMPRODUCT is broken (I swear!)

    Guess your word of honor isn't very good.
    Come again soon, NOT.

    --
    Gary Brown


    "brandonc" wrote:

    > Hi all-
    >
    > Of course SUMPRODUCT is not broken, but I had to get your attention somehow.
    >
    > Hoping you can help with a problem I've been kicking around for a while now.
    > This problem is part of a larger capacity planning tool I am creating.
    >
    > ** Two tables I have:
    > 1. Use Case & Interface ID pairs, along with % usage of the interface. A
    > single Use Case can access many Interfaces.
    > 2. Load per Use Case ID.
    >
    > ** What I want to calculate:
    > 1. Load per interface ID. Over all Use Cases for this Interface, sum the
    > product of interface usage (%) and Use Case load.
    >
    > For this post, I threw all the tables on one sheet to make it easier to talk
    > about.
    >
    > ** A1:C7 is the Use Case & Interface pairs with interface usage %:
    > Column A: {Use Case Id, UC1, UC2, UC2, UC3, UC3, UC3}
    > Column B: {Interface Id, Int2, Int1, Int7, Int7, Int5, Int2}
    > Column C: {% Interface Usage, 100%, 10%, 90%, 80%, 5%, 15%}
    >
    > ** E1:F4 is the load per Use Case
    > Column E: {Use Case Id, UC1, UC2, UC3}
    > Column F: {Load per Use Case, 38000, 1500, 125}
    >
    > ** H1:I5 is the Interface load
    > Column H: {Interface ID, Int1, Int2, Int5, Int7}
    > Column I: {Load per Interface, <formula I need help with>}
    >
    >
    > I did the calculations by hand, and here is the load per interface I would
    > expect:
    > Int1=150, Int2=38018.75, Int5=6.25, Int7=1450
    >
    > I've tried playing around with the usual suspects for a while, SUMPRODUCT,
    > SUMIF, & VLOOKUP, but this one has me stumped. Any help is greatly
    > appreciated!
    >
    > Thanks!
    > -brandon
    >


  8. #8
    brandonc
    Guest

    Re: SUMPRODUCT is broken (I swear!)

    "Domenic" wrote:

    > Here's another way...
    >
    > =SUMPRODUCT(SUMIF(E$2:E$4,A$2:A$7,F$2:F$4),--(B$2:B$7=H2),C$2:C$7)
    >
    > ....which doesn't require E2:E4 to be sorted in ascending order.
    >
    > Hope this helps!


    Heck yes it does! SWEEET!

    Now if only I could repay you somehow. I don't have many skills though. I
    can draw, but it will take me at least three hours to shade your upper lip.
    Let me know if you would like to commission a portrait.

    Thanks again!
    -brandon

  9. #9
    Biff
    Guest

    Re: SUMPRODUCT is broken (I swear!)

    >whipped up the Excel bug-fix team into a frenzy.

    Ha!

    There is no such team and even if there were, "frenzy" would not describe
    their level of activity!

    Biff

    "brandonc" <brandonc@discussions.microsoft.com> wrote in message
    news:57860A08-61B9-4A48-9A9A-27A28E2BC6C8@microsoft.com...
    >
    >
    > "Gary L Brown" wrote:
    >
    >> Guess your word of honor isn't very good.
    >> Come again soon, NOT.
    >>
    >> --
    >> Gary Brown
    >>
    >>
    >> "brandonc" wrote:
    >>
    >> > Hi all-
    >> >
    >> > Of course SUMPRODUCT is not broken, but I had to get your attention
    >> > somehow.
    >> >

    >
    > My apologies if I needlessly whipped up the Excel bug-fix team into a
    > frenzy. Stand down! Stand down!
    >
    > I was actually poking fun at other similar posts I've seen claiming an
    > Excel
    > bug must be the only explanation for their quirky results. Only to find
    > out
    > they just didn't know how to structure the formula in the first place.
    >
    > So Gary, have an answer for me, or just here to shoo me away?




Closed 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1