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

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

"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?

"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?

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 <[email protected]>,
"brandonc" <[email protected]> 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

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

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
>

"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

>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" <[email protected]> wrote in message
news:[email protected]...
>
>
> "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?