Forum Discussion
Using the right functions
I am creating an inventory spreadsheet (Inventory)using info from another tab(Production).
Basically, anytime the word "Plastic1048" is in D column it should subtract the production and scrap in that column from the beginning inventory on(EXPER).
I can make it work using the If function but it will only calculate 1 row. If there are multiple times "Plastic1048" is being used in the column, it still will only calculate one time.
=IF('Production '!D2:D21="Plastic4078",C2-'Production '!E2-'Production '!F2,C2
It looks like you're using google sheets. I can't remember if Sumproduct() works the same way in that software.
Try this:
=C2-SUMPRODUCT((EXPER!$D$2:$D$16="Plastic4078")*EXPER!$E$2:$F$16)
or this:
=C2-SUMPRODUCT((EXPER!$D$2:$D$16="Plastic4078"),(EXPER!$E$2:$F$16))
or this:
=C2-IF(EXPER!D2="Pastic4078", Sum(EXPER!E2:F2),0)
6 Replies
Hey Mike-
Hope that you're doing well. Maybe try something like this:
If this isn't quite what you're looking for maybe you can modify my attached example workbook to reflect your data structure. Not sure I comprehend what the data looks like exactly. Sounds like it will be a quick fix though:
- Thanks Matt for the response.
I have inserted a few photos of the spreadsheets.
- When Material (Column D) = "Plastic4001" then deduct E and F on that row from I2 (other spreadsheet)
Thank-you for your time It looks like you're using google sheets. I can't remember if Sumproduct() works the same way in that software.
Try this:
=C2-SUMPRODUCT((EXPER!$D$2:$D$16="Plastic4078")*EXPER!$E$2:$F$16)
or this:
=C2-SUMPRODUCT((EXPER!$D$2:$D$16="Plastic4078"),(EXPER!$E$2:$F$16))
or this:
=C2-IF(EXPER!D2="Pastic4078", Sum(EXPER!E2:F2),0)
