Sum Product

[markoakes]

I need help with a Sum Product formula I think.

I need a formula in Column B Sheet1 to lookup a Customer Number in Column A Sheet1 on column B Sheet2 and if the corresponding value in Column C Sheet2 =”Yes” then sum the value in Column D Sheet 2.

So the customer on Sheet1 may have multiple rows on Sheet2, some “Yes” some “No”. I need a total of all “Yes” for that Customer.

 

[Robert Mika]

Excel 2003
=SUMPRODUCT(--(Sheet2!$B$2:$B$50=Sheet1!A1),--(Sheet2!$C$2:$C$50="Yes")*(Sheet2!$D$2:$D$50))


Excel 2007/10


=SUMIFS(Sheet2!$D$2:$D$50,Sheet2!$B$2:$B$50,Sheet1!A1,Sheet2!$C$2:$C$50,"Yes")