Help with Formula

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

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 !!!
 
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.
 
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???
 
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.
 
Back
Top