Help with Formula

[Guest]

Sheet1 Customer Level
Custom Level
David Gold
James Silver
Brian Gold
Kelly Silver

Sheet2 Discount %
Product Product ID Gold Silver
T-Mobile $10 710 10% 8%
T-Mobile $25 725 15% 13%
T-Mobile $50 750 17% 15%
Cingular $10 310 10% 7%
Cingular $20 320 15% 12%
Cingular $50 350 20% 18%

Sheet3 Report
Customer Product Product ID Denom
Brian T-Mobile $10 710 $10
Brian Cingular $20 320 $20
James Cingular $50 350 $50
David Cingular $10 310 $10
Kelly T-Mobile $25 725 $25
Brian Cingular $50 350 $50
James T-Mobile $25 725 $25

Sheet4 Summary
Customer Total Sold Value Total Discount
David $10
James $75
Brian $80
Kelly $25

Product has different discount rates
Different Customer has different discount rates

Please somebody come up with some formula !!!

 

[Guest]

Sorry I forgot to put what I'm trying to do

Need to find out Total Discount in $

 

[Guest]

Assuming all of your tables begin in cell A1 of each sheet, I would set up an
extra column in Sheet3 (Column E) to compute the discount on each individual
item.

=INDEX(Sheet2!$A$1:$D$7,MATCH($B2,Sheet2!$A$1:$A$7,0),MATCH(VLOOKUP($A2,Sheet1!$A$2:$B$5,2,0),Sheet2!$A$1:$D$1,0))

Then on sheet4, I would use

=SUMPRODUCT(--(Sheet3!$A$2:$A$8=A2),Sheet3!$D$2:$D$8,Sheet3!$E$2:$E$8)

In VBA, you have to use Evaluate with SUMPRODUCT. If you search this site
for Evaluate you should find some examples of how to do so.

 

[Guest]

WOW it works perfect Thank you Thank you Thank you
1 more question let's say I can't add that extra column on sheet3 is there
any way you could creat a formula???

 

[Guest]

I didn't have much luck in doing it all in one array formula. The formula
returned results, but they weren't always correct or predictable.