What Formula to use(VBA will be great to)
 

Need to find out Total Discount in Sheet 4 in $ Amount

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
Custome 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 !!!
If you could give me VBA code it will be awesome but if not still fine

Do not know which formula to use
 

Need to find out Total Discount in Sheet 4 in $ Amount

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 !!!
If you could give me VBA code it will be awesome but if not still fine

What Formula to use(VBA will be great to)
 

Try the following:

In Sheet 3, add "Discount" and "Discount Amount" in columns E & F

in E2:

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

in F2

=D2*E2

(THese could be combined into a single column)

in Sheet4 :

in C2:

=SUMIF(Sheet3!$A$2:$A$8,Sheet4!A2,Sheet3!$F$2:$F$8 )

Copy all formulas down as required.

HTH


"Need help with sumif" wrote:

Need to find out Total Discount in Sheet 4 in $ Amount

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
Custome 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 !!!
If you could give me VBA code it will be awesome but if not still fine