# sum if 2 conditions are met

#### Fawlty1

##### Board Regular
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 -

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
try this:

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

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.

So - working as you need now?

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

Replies
4
Views
222
Replies
9
Views
144
Replies
4
Views
296
Replies
8
Views
157
Replies
1
Views
449

1,220,987
Messages
6,157,230
Members
451,407
Latest member
vdaesety

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back