software

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 amp; 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 amp; 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, lt;formula I need help withgt;}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, amp; 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
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=506497
quot;daddylonglegsquot; wrote:
gt;
gt; You could use this formula in I2 copied down to I5
gt;
gt; =SUMPRODUCT(--(B$2:B$7=H2),C$2:C$7,LOOKUP(A$2:A$7,E$2:F$4))
gt;
gt; Note because of use of LOOKUP E2:E4 must be ascending order
gt;

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?



quot;Gary L Brownquot; wrote:

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

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 gt;,
quot;brandoncquot; gt; wrote:

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


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

ah Domenic, very nice

all I could think of was to use another column.......--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=506497Guess your word of honor isn't very good.
Come again soon, NOT.

--
Gary Brownquot;brandoncquot; wrote:

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

quot;Domenicquot; wrote:

gt; Here's another way...
gt;
gt; =SUMPRODUCT(SUMIF(E$2:E$4,A$2:A$7,F$2:F$4),--(B$2:B$7=H2),C$2:C$7)
gt;
gt; ....which doesn't require E2:E4 to be sorted in ascending order.
gt;
gt; 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

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

Ha!

There is no such team and even if there were, quot;frenzyquot; would not describe
their level of activity!

Biff

quot;brandoncquot; gt; wrote in message
...
gt;
gt;
gt; quot;Gary L Brownquot; wrote:
gt;
gt;gt; Guess your word of honor isn't very good.
gt;gt; Come again soon, NOT.
gt;gt;
gt;gt; --
gt;gt; Gary Brown
gt;gt;
gt;gt;
gt;gt; quot;brandoncquot; wrote:
gt;gt;
gt;gt; gt; Hi all-
gt;gt; gt;
gt;gt; gt; Of course SUMPRODUCT is not broken, but I had to get your attention
gt;gt; gt; somehow.
gt;gt; gt;
gt;
gt; My apologies if I needlessly whipped up the Excel bug-fix team into a
gt; frenzy. Stand down! Stand down!
gt;
gt; I was actually poking fun at other similar posts I've seen claiming an
gt; Excel
gt; bug must be the only explanation for their quirky results. Only to find
gt; out
gt; they just didn't know how to structure the formula in the first place.
gt;
gt; So Gary, have an answer for me, or just here to shoo me away?