Inventory Control Formula

[Bedford]

At an attempt to create a inventory control sheet, we have columns “Qty IN” and “Qty OUT”, user enters the quantities as they apply to the task, date, time, name are collected.

Problem, each time a product code is removed from inventory, and there are hundreds of product codes, how can we have a formula that tally’s in real time the balance of stock on any particular product code? Is it a combination SUM, VLOOKUP, INDEX formula, and what would that look like in the "Qty In Stock" column?

Help please.

Check_in.xlsx
	A	B	C	D	E	F	G	H	I	J	K
8	Description	Part#	Date IN	Time IN	Qty IN	Date OUT	Time OUT	Qty OUT	Checked in By	Checked out By	Qty in Stock
9	FoldawayMX	MX2-1050x2000-W	February 8, 2022	12:00:00 PM	10	February 8, 2022	12:57:00 PM		Doug		10
10	FoldawayMX	MX2-1050x2000-W	February 10, 2022	6:56:00 AM		February 10, 2022	6:56:00 AM	1		Doug	-1
Sheet1

Cell Formulas
Range	Formula
K9:K10	K9	=SUM(IFERROR([@[Qty IN]]-[@[Qty OUT]],""))

Cells with Data Validation
Cell	Allow	Criteria
A9:A41	List	='Dependent Drop-down DATA'!$A$1:$AL$1
B9:B41	List	=INDIRECT($A9)
I9:J10	List	=Name

 

[6StringJazzer]

In your example, there is only one product code. I am assuming that you have more than one in the same table. In that case you have the right idea in K but need

=SUMIFS([Qty IN],[Part'#],[@[Part'#]])-SUMIFS([Qty OUT],[Part'#],[@[Part'#]])

 

[Bedford]

In your example, there is only one product code. I am assuming that you have more than one in the same table. In that case you have the right idea in K but need

=SUMIFS([Qty IN],[Part'#],[@[Part'#]])-SUMIFS([Qty OUT],[Part'#],[@[Part'#]])

Jeff, you nailed it, awesome! Can't thank you enough.
Doug.

 

[6StringJazzer]

Only too glad to help