Excel Match and Sum Problem

vivienne3608

New member
Joined
Oct 23, 2014
Messages
7
Reaction score
0
Points
0
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
 

Attachments

  • Excel Problem2.xls
    22 KB · Views: 16
if in E18 to j18 you copy the colours then the formula you require is

E19 =D19 * SUMPRODUCT(INDEX($B$3:$O$8,MATCH(E$18,$A$11:$A$16,0),)*($B$1:$O$1=$A19)*($B$2:$O$2=$B19))
 
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 (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

mrexcel.com/forum/excel-questions/814498-excel-match-sum-problem.html#post3979362
excelforum.com/excel-formulas-and-functions/1045968-excel-match-and-sum-problem.html
excelexperts.com/excel-match-and-sum-problem
 
Hello,

Thank you to PGC01, post #3 (mrexcel.com/forum/excel-questions/814498-excel-match-sum-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
 
Back
Top