Live Inventory Count

Biiams

Hello,

I am attempting to create a sheet that can track chemical inventory based on type of chemical and container size. I would like a "live" inventory count to populate in the Qty Remaining but am struggling on figuring out the formula to use and how to set it up.

I think I needs a SUMIFS formula but am getting stuck on how to select the cells so it pulls correctly. I added an In/Out column so the formula could detect if "out" is selected the number in the "Qty" column needs to be subtracted and if "in" is selected the number in the Qty column needs to be added to the Qty Remaining column.

Paul Newcome

You would use something like this:

=SUMIFS(QTY:QTY, [In/Out]:[In/Out], @cell = "In", Chemical:Chemical, @cell = Chemical@row, [Container Size]:[Container Size], @cell = [Container Size]@row, Date:Date, @cell <= Date@row) - SUMIFS(QTY:QTY, [In/Out]:[In/Out], @cell = "Out", Chemical:Chemical, @cell = Chemical@row, [Container Size]:[Container Size], @cell = [Container Size]@row, Date:Date, @cell <= Date@row)

The above should give you your running total.

Paul Newcome

Is the Date column a system generated "Created Date" type of column? How exactly are new rows being added?

Biiams

@Paul Newcome the date column is a created date column when a form is submitted by the employee who would be placing new inventory into this grid or out when they remove it. The data for this grid would be captured through form submission, I just need the Qty remaining column to track all the submissions so I can get a live count on a dashboard.

Paul Newcome

You would use something like this:

=SUMIFS(QTY:QTY, [In/Out]:[In/Out], @cell = "In", Chemical:Chemical, @cell = Chemical@row, [Container Size]:[Container Size], @cell = [Container Size]@row, Date:Date, @cell <= Date@row) - SUMIFS(QTY:QTY, [In/Out]:[In/Out], @cell = "Out", Chemical:Chemical, @cell = Chemical@row, [Container Size]:[Container Size], @cell = [Container Size]@row, Date:Date, @cell <= Date@row)

The above should give you your running total.

Biiams

@Paul Newcome that seems to be exactly what I am needing. Thank you for your help on this!

Paul Newcome

Happy to help. 👍️