| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| assign formula to another cell | Excel Worksheet Functions | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Creating a check mark box | Setting up and Configuration of Excel | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
