sum if 2 conditions are met

[Fawlty1]

I am trying to make a template consisting of sheet1 (Product) Sheet2 (Product 2) and sheet3 (Return info)- Sheets(Product & Product 2) will of course be renamed by the user with the name of the product involved. -
- Sheet 'Return info' shows store #'s, Product and Units returned - Sheet 'Product'shows Invoice #'s, Store #'s, Units Returned, etc there is also a cell where the user will enter the product name. I'm trying to come up with a formula that will sum the total returns for a given store and product shown in sheet 'Return info' and put that info on sheet (Product) in the cell 'Units Returned' - - - Below is the formula I tried but it is not working correctly. Can someone see what I am doing wrong or have another formula I could use. Thanks for any suggestions----
=SUM(('RETURN INFO'!B$2:B$150=PRODUCT!A9)*('RETURN INFO'!C3:C150=PRODUCT!D$2)*'RETURN INFO'!D$2:D$150) I then pressed CTRL-SHIFT-ENTER -

 

[PaddyD]

try this:

=SUMPRODUCT(('RETURN INFO'!B$2:B$150=PRODUCT!A9)*('RETURN INFO'!C2:C150=PRODUCT!D$2)*'RETURN INFO'!D$2:D$150)

Paddy

 

[Fawlty1]

Thanks Paddy for your fast reply, I tried your suggestion and at 1st it seemed to work but when i copied formula down several rows it didnt work correctly. I then noticed that I was missing an $ in the formula.

 

[PaddyD]

So - working as you need now?

Paddy

 

[Fawlty1]

formula now works as it should & All is right with the universe. Long Live Excel.