# Excel spreadsheet problem

##### New Member
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
 ​ ​ ​ ​

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Logit

##### Well-known Member
Look at multiple "IFs" in a formula.

##### New Member
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.

Replies
3
Views
103
Replies
11
Views
385
Replies
1
Views
143
Replies
7
Views
58
Replies
3
Views
205

1,148,191
Messages
5,745,251
Members
423,939
Latest member
Learnexcel1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back