SUMPRODUCT is broken (I swear!)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 orde
 
daddylonglegs said:
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
Click to expand...

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 said:
Guess your word of honor isn't very good.
Come again soon, NOT.
Click to expand...

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!
 
Domenic said:
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.
Click to expand...

ah Domenic, very nice:)

all I could think of was to use another column......
 
Domenic said:
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!
Click to expand...

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
 
Back
Top