Excel spreadsheet problem

[saad_mce]

Hi all,

I have got a spreadsheet problem that I need the help of the wizards in this forum.
I download this data from our ERP system on finished goods SKUs. There few hundred SKUs.
The data up to column H is from the ERP system. The data starts from today's date to show the on hand stock today. It then goes on to show the stock consumption by customer orders and stock increase by production.
The type of demand is noted in column C and the stock amount consumed by demand or added by production is shown is column G.

I use a sumifs formula in the 1st calculated column (column I) to calculate the available quantity after each addition/subtraction of stock (SUMIFS(G$2:G2,A$2:A2,$A2)).

The solutions required for the below issues. The spreadsheet has 100s of SKUs sorted by SKU code (column A) and date of activity of that SKU (column D).

1. My problem starts with 2nd calculated column (column J). I want to run a formula that will be blank if we have stock today and "OOS now" if we have zero or negative stock today. I can do this formula alone, but could not find out how to combine with the next set of problems.

2. If we go out of stock in future before next production or between two productions, I want the column to say "POOS before Prod" (potential out of stock). This requires looking at the available Qty column (column I) and Rec/reqd quantity (column G)

3. If we have no prod planned production for this SKU, I want the formula to say "POOS & no Prod Planned". Again the formula needs to look in the negatives of column I and if there are any receipts in column G.

Any suggestion will be greatly appreciated.

A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
Material	Material Description	MRP element	Planned dates	MRP element data	Exception Message	Rec./reqd quantity	Rescheduling date	Available Qty	Issues
10007​	Product 1	Stock	26/05/2021​			3,960		3,960
10007​	Product 1	Demand	28/05/2021​	0010722781/00010		- 1,512		2,448
10007​	Product 1	Demand	27/08/2021​	0010679907/00010		- 1,512		936
10007​	Product 1	Demand	27/10/2021​	0010772594/00010		- 1,512		- 576	POOS before Prod
10007​	Product 1	PldOrd	29/10/2021​	0001489363/STCK**	10​	6,048	27/10/2021​	5,472	Production
10017​	Product 2	Stock	26/05/2021​			-		-	OOS now
10017​	Product 2	Demand	28/05/2021​	0010841763/00010		- 12,096		- 12,096	POOS before Prod
10017​	Product 2	PrcOrd	1/06/2021​	000000908773/YFIL/Re	10​	12,096	28/05/2021​	-	Prod
10017​	Product 2	Demand	27/09/2021​	0010840748/00010		- 12,096		- 12,096	POOS before Prod
10017​	Product 2	PldOrd	29/10/2021​	0001426462/STCK**	10​	12,096	27/09/2021​	-	Prod
10020​	Product 3	Stock	26/05/2021​			5,000		5,000
10020​	Product 3	Demand	28/05/2021​	0010841763/00010		- 12,096		- 7,096	POOS & no Prod Planned
10020​	Product 3	Demand	1/06/2021​	0010841763/00011	10​	- 12,096	28/05/2021​	- 19,192	POOS & no Prod Planned
10020​	Product 3	Demand	27/09/2021​	0010841763/00012		- 12,096		- 31,288	POOS & no Prod Planned
10020​	Product 3	Demand	29/10/2021​	0010841763/00013	10​	- 12,096	27/09/2021​	- 43,384	POOS & no Prod Planned

​

​

​

​

 

[Logit]

Look at multiple "IFs" in a formula.

 

[saad_mce]

Hi,
I tried multiple ifs. Problem is the formula has to look not just at the same row, but to the whole column for two columns. This probably requires combining array with sumifs.