Forum Discussion

Copper Contributor

Jun 27, 2018

Solved

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 tha...

Formulas & Functions

Need Help

Matt Mickle
Jun 28, 2018
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)

Mike Alford

Copper Contributor

Jun 28, 2018

Matt Mickle

Bronze Contributor

Jun 28, 2018

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)

Mike Alford
Copper Contributor
Jun 28, 2018
Matt,
It Worked!!!
=C2-SUMPRODUCT((EXPER!$D$2:$D$16="Plastic4078")*EXPER!$E$2:$F$16)
One last question...
The last step...
I have multiple tabs that the Inventory Spreadsheet is pulling from.
The Spreadsheet itself is the same except the info that is entered in the specific columns.
In other words EXPER! is one tab that it pulls from but also EXPER1! and EXPER2! ,and so forth.
It works perfectly but how do you incorporate the other tabs?

You've been a great help... I have spent hours on Excel help and YouTube trying to figure this out.
- Matt Mickle
  Bronze Contributor
  Jun 28, 2018
  Not sure how many you have.... but if you have a large number you may want to create an additional tab with the subtotals from each sheet and reference them directly.
  
  So you would have the summary of all of these values on one sheet and then you
  
  If you don't have that many than you could do this:
  
  =C2-SUMPRODUCT((EXPER!$D$2:$D$16="Plastic4078")*EXPER!$E$2:$F$16)-SUMPRODUCT((EXPER1!$D$2:$D$16="Plastic4078")*EXPER1!$E$2:$F$16)