Search code examples
excelmatchsumifs

Excel SUMIFS & Match

I am trying to calculate the price of material using there Material ID and Name. I have three different tables (see below) I am trying to calculate the total price. I am trying to come up with a formula that is pretty much = QTY*Price (IF drawing number & Mat ID match)

I colored coded the pairs I need to match, the price and QTY are the ones I need to multiply to get the total price, im just trying to automate this because i have around 3000 drawings to calc prices for.

Any Help is greatly appreciated.

enter image description here

Solution

  • Use INDEX/MATCH:

    =INDEX(I:I,MATCH(A2,G:G,0))*INDEX(E:E,MATCH(INDEX(H:H,MATCH(A2,G:G,0)),D:D,0))

    enter image description here

    If there are more than one to one relationship between the Drawing and the material then we can use the dynamic array formula FILTER

    =SUMPRODUCT(SUMIFS(E:E,D:D,FILTER(H:H,G:G=A2))*FILTER(I:I,G:G=A2))

    If one does not have the dynamic array formula then we need add another boolean to the sumproduct:

    =SUMPRODUCT(SUMIFS(E:E,D:D,$H$2:$H$6)*$I$2:$I$6*($G$2:$G$6=A2))

    enter image description here