Excel Match and Sum Problem
Good afternoon,
I am hoping someone can help me with a problem I have. I have changed the actual details of the issue but the fundamentals are the same.
I want to add the total cost of a particular item, in this example red balloons. Explanations of cells are as follows:
Cells B1:O1 = Item
Cells B2:O2 = Quantity of items in an order
Cells B3:O8 = Number of orders for each item and quantity
Cells B11:C16 = Rate item charged at
Cells C19:D32 = Cost per quantity of item
Excel needs to look at the rate charged for the item (Red Balloon = Higher) then sum costs per order quantity. This should be:
Red Balloon 1 20 x £20 = £400
Red Balloon 2 20 x £21 = £420
Red Balloon 3 10 x £22 = £440
Red Balloon 4 10 x £23 = £460
Red Balloon 5 20 x £24 = £480
Red Balloon 6 30 x £25 = £500
Red Balloon 7 22 x £26 = £520
Total cost cell R3 = £3,220.
Any help is greatly appreciated.
Thank you
Vivienne
| Attachment | Size |
|---|---|
| Excel Problem2.xls | 22 KB |
Formula Correct
Hello,
Thank you to PGC01, post #3 (http://www.mrexcel.com/forum/excel-questions/814498-excel-match-sum-prob...) as the following formula now works:
=SUMPRODUCT(--($B$1:$O$1=R1),INDEX($B$3:$O$8,MATCH(R2,$A$3:$A$8,0),0),INDEX($B$22:$O$23,MATCH(INDEX($B$11:$C$16,MATCH(R2,$A$11:$A$16,0 ),MATCH(R1,$B$10:$C$10,0)),$A$22:$A$23,0),0))
I no longer need any further replies to this issue. Thank you to all who have taken the time to try and find a solution for me I appreciate it.
Vivienne
Links
Good morning,
Please find below links to other excel forums containing the same post. I am still working on the answers I have been given. The reply from Ace_XL (www.excelforum.com) works on the attached worksheet, however, the actual spreadsheet I am working with is much more complex so is taking a little longer to ensure it works correctly. I will update my post when I have completed this.
I apologise to all for not including the links sooner.
Vivienne
http://www.excelguru.ca/forums/showthread.php?3655-Excel-Match-and-Sum-P...
http://www.mrexcel.com/forum/excel-questions/814498-excel-match-sum-prob...
http://www.excelforum.com/excel-formulas-and-functions/1045968-excel-mat...