| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct dvision
Good morning!
I am using Sumproduct in my spreadsheet. I am trying to do a division within. Here is my problem. Location Product volumn Amount I need to match the location and product, once that is done, then I need to do sum of volume * (sum of amount / sum of volume). Hope this makes sense. Thanks, Rich |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct dvision
Try
=sumproduct(--(a1:a100="Location"),--(b1:b100="Product),(c1:c100)/(sumproduct(--(a1:a100="Location"),--(b1:b100="Product),(c1:c100) + sumproduct(--(a1:a100="Location"),--(b1:b100="Product),(d1:d100)) No error checking provided. "MrRJ" wrote: Good morning! I am using Sumproduct in my spreadsheet. I am trying to do a division within. Here is my problem. Location Product volumn Amount I need to match the location and product, once that is done, then I need to do sum of volume * (sum of amount / sum of volume). Hope this makes sense. Thanks, Rich |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct dvision
Perhaps further clarification on your formula is needed. Sum of volume * (Sum
of Amount / Sum of Volume) will be equal to Sum of Amount. -- John C "MrRJ" wrote: Good morning! I am using Sumproduct in my spreadsheet. I am trying to do a division within. Here is my problem. Location Product volumn Amount I need to match the location and product, once that is done, then I need to do sum of volume * (sum of amount / sum of volume). Hope this makes sense. Thanks, Rich |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct dvision
John,
sorry about that. Here is further clarification. Perhaps I can state it this way. I need the sum of volume x rate. This is based on 2 criteria I set for location and Product. Location Product Volume Rate Hope this clears it? Rich "John C" wrote: Perhaps further clarification on your formula is needed. Sum of volume * (Sum of Amount / Sum of Volume) will be equal to Sum of Amount. -- John C "MrRJ" wrote: Good morning! I am using Sumproduct in my spreadsheet. I am trying to do a division within. Here is my problem. Location Product volumn Amount I need to match the location and product, once that is done, then I need to do sum of volume * (sum of amount / sum of volume). Hope this makes sense. Thanks, Rich |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct dvision
Assuming I understand you correctly....
=SUMPRODUCT(--($A$2:$A$100=location),--($B$2:$B$100=product),($C$2:$C$100)*($D$2:$D$100)) Hope this helps. -- John C "MrRJ" wrote: John, sorry about that. Here is further clarification. Perhaps I can state it this way. I need the sum of volume x rate. This is based on 2 criteria I set for location and Product. Location Product Volume Rate Hope this clears it? Rich "John C" wrote: Perhaps further clarification on your formula is needed. Sum of volume * (Sum of Amount / Sum of Volume) will be equal to Sum of Amount. -- John C "MrRJ" wrote: Good morning! I am using Sumproduct in my spreadsheet. I am trying to do a division within. Here is my problem. Location Product volumn Amount I need to match the location and product, once that is done, then I need to do sum of volume * (sum of amount / sum of volume). Hope this makes sense. Thanks, Rich |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct dvision
Hi John,
Take a look what I have. The number given is incorrect. What did I do wrong. The values for column C is 166.250 83.120 124.680 249.370 249.370 124.680 290.930 166.250 207.810 The values for column D a $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 Using this formula, I get the value of 88,648.56. I should have 32,717.21. Basically, I need the sum of C x D. Does that make sense? =SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$A$2:$A$11818=B227),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$C$2:$C$11818=A227),'[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$P$2:$P$11818)*'[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$M$2:$M$11818 "John C" wrote: Assuming I understand you correctly.... =SUMPRODUCT(--($A$2:$A$100=location),--($B$2:$B$100=product),($C$2:$C$100)*($D$2:$D$100)) Hope this helps. -- John C "MrRJ" wrote: John, sorry about that. Here is further clarification. Perhaps I can state it this way. I need the sum of volume x rate. This is based on 2 criteria I set for location and Product. Location Product Volume Rate Hope this clears it? Rich "John C" wrote: Perhaps further clarification on your formula is needed. Sum of volume * (Sum of Amount / Sum of Volume) will be equal to Sum of Amount. -- John C "MrRJ" wrote: Good morning! I am using Sumproduct in my spreadsheet. I am trying to do a division within. Here is my problem. Location Product volumn Amount I need to match the location and product, once that is done, then I need to do sum of volume * (sum of amount / sum of volume). Hope this makes sense. Thanks, Rich |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct dvision
Well, when I take the values you gave, and use SUMPRODUCT on them, it
calculates just fine. Your formula looks fine, but I do have one question regarding it. Your first portion is checking the range $a$2:$a$11818, and seeing if it is equal to a value in cell B227, but your second portion is checking the range in $c$2:$c$11818, and checking to see if it is equal to the value in A227(which is part of the first range). Is this correct? Also, I am assuming that the other workbook is open when this calculation is done. -- John C "MrRJ" wrote: Hi John, Take a look what I have. The number given is incorrect. What did I do wrong. The values for column C is 166.250 83.120 124.680 249.370 249.370 124.680 290.930 166.250 207.810 The values for column D a $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 Using this formula, I get the value of 88,648.56. I should have 32,717.21. Basically, I need the sum of C x D. Does that make sense? =SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$A$2:$A$11818=B227),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$C$2:$C$11818=A227),'[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$P$2:$P$11818)*'[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$M$2:$M$11818 "John C" wrote: Assuming I understand you correctly.... =SUMPRODUCT(--($A$2:$A$100=location),--($B$2:$B$100=product),($C$2:$C$100)*($D$2:$D$100)) Hope this helps. -- John C "MrRJ" wrote: John, sorry about that. Here is further clarification. Perhaps I can state it this way. I need the sum of volume x rate. This is based on 2 criteria I set for location and Product. Location Product Volume Rate Hope this clears it? Rich "John C" wrote: Perhaps further clarification on your formula is needed. Sum of volume * (Sum of Amount / Sum of Volume) will be equal to Sum of Amount. -- John C "MrRJ" wrote: Good morning! I am using Sumproduct in my spreadsheet. I am trying to do a division within. Here is my problem. Location Product volumn Amount I need to match the location and product, once that is done, then I need to do sum of volume * (sum of amount / sum of volume). Hope this makes sense. Thanks, Rich |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct dvision
Oh, and also to clarify, you are actually wanting the values in column P
multiplied by column M, according to your formula, is that also correct? -- John C "MrRJ" wrote: Hi John, Take a look what I have. The number given is incorrect. What did I do wrong. The values for column C is 166.250 83.120 124.680 249.370 249.370 124.680 290.930 166.250 207.810 The values for column D a $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 $19.68 Using this formula, I get the value of 88,648.56. I should have 32,717.21. Basically, I need the sum of C x D. Does that make sense? =SUMPRODUCT(--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$A$2:$A$11818=B227),--('[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$C$2:$C$11818=A227),'[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$P$2:$P$11818)*'[CCDS_PEP_Audit_Transaction_Receipt_Report_PAS_P920 08_090808.xls]PAS P9 wkst'!$M$2:$M$11818 "John C" wrote: Assuming I understand you correctly.... =SUMPRODUCT(--($A$2:$A$100=location),--($B$2:$B$100=product),($C$2:$C$100)*($D$2:$D$100)) Hope this helps. -- John C "MrRJ" wrote: John, sorry about that. Here is further clarification. Perhaps I can state it this way. I need the sum of volume x rate. This is based on 2 criteria I set for location and Product. Location Product Volume Rate Hope this clears it? Rich "John C" wrote: Perhaps further clarification on your formula is needed. Sum of volume * (Sum of Amount / Sum of Volume) will be equal to Sum of Amount. -- John C "MrRJ" wrote: Good morning! I am using Sumproduct in my spreadsheet. I am trying to do a division within. Here is my problem. Location Product volumn Amount I need to match the location and product, once that is done, then I need to do sum of volume * (sum of amount / sum of volume). Hope this makes sense. Thanks, Rich |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct dvision
=SUM(IF((A2:A20="Location")*(B2:B20="Product"),C2: C20/D2:D20))
this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter -- __________________________________ HTH Bob "MrRJ" wrote in message ... Good morning! I am using Sumproduct in my spreadsheet. I am trying to do a division within. Here is my problem. Location Product volumn Amount I need to match the location and product, once that is done, then I need to do sum of volume * (sum of amount / sum of volume). Hope this makes sense. Thanks, Rich |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct dvision
Hello again,
I have had computer problems. I will re-check with the formula's that you all have submitted. Thanks for your help for now. I will get back to you all. Rich "Bob Phillips" wrote: =SUM(IF((A2:A20="Location")*(B2:B20="Product"),C2: C20/D2:D20)) this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter -- __________________________________ HTH Bob "MrRJ" wrote in message ... Good morning! I am using Sumproduct in my spreadsheet. I am trying to do a division within. Here is my problem. Location Product volumn Amount I need to match the location and product, once that is done, then I need to do sum of volume * (sum of amount / sum of volume). Hope this makes sense. Thanks, Rich |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| SumProduct Help | Excel Worksheet Functions | |||
| Sumproduct | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| IF/AND used with SUMPRODUCT? | Excel Worksheet Functions | |||
Hybrid Mode
