# Thread: Excel Match and Sum Problem

1. ## 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 32 = 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  Reply With Quote

2. 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))  Reply With Quote

3. 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  Reply With Quote

4. 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  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•