Results 1 to 4 of 4

Thread: Excel Match and Sum Problem

  1. #1

    Excel Match and Sum Problem

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 C1932 = 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

    Attached Files Attached Files

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365
    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))

  3. #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 ( 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.


  4. #4

    Thank you to PGC01, post #3 ( 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.


Posting Permissions

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