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

Thank you both for your quick replies. Ace XL your formula works and it should be 3,072. Can't even get that bit correct.

Again Thank you very much.

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://excelexperts.com/excel-match-and-sum-problem
http://www.excelguru.ca/forums/showt...nd-Sum-Problem
http://www.mrexcel.com/forum/excel-q...m-problem.html

XOR LX,

I am indeed . I am aware that each forum has different users. I wanted to try and get it out to all and see different view points. There have been so many different formulas suggested and it is interesting to see the approaches taken. I am learning a great deal from other peoples expertise, which is great.

Vivienne

Originally Posted by XOR LX

Wow! You've certainly hedged your bets there, then!

Thanks for letting us know.

Perhaps if you were able to explain in a little more detail what issues you're having with adapting Ace_XL's solution to your actual workbook? Or even posting that workbook (replacing confidential information with dummy data, if necessary)?

Regards

Hello,

Thank you to PGC01, post #3 (http://www.mrexcel.com/forum/excel-q...m-problem.html) 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