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