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
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=506497

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

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



ah Domenic, very nice:)

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=506497

"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

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