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 !!!

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

Need to find out Total Discount in $

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.



"Need help with sumif" wrote:

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

Need to find out Total Discount in $

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 !!!

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

"JMB" wrote:

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.



"Need help with sumif" wrote:

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

Need to find out Total Discount in $

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 !!!

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.

"Need help with sumif" wrote:

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

"JMB" wrote:

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.



"Need help with sumif" wrote:

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

Need to find out Total Discount in $

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 !!!