Spreadsheet: 124307d1318324906-sumproduct-criteria-problem-capex-and-opex-stock-distribution-bc3
Sheet: Sheet1

 |  | Opex Opening Balance | Capex Opening Balance |  |  | ID No. | opex open stock | opex stock in | opex stock out | Opex Running Stock |  | capex open stock | capex stock in | capex stock out | Capex Running Stock | Total Running Stock
 | 001 | 1 | 3 |  |  | 001 | 1 | 2 | 1 | 2 |  | 1 | 3 | 4 | 0 | 2
 | 002 | 2 | 2 |  | Adjusted | 001 | 1 | 2 | 0 | 3 |  | 3 | 3 | 5 | 1 | 4
 | 003 | 3 | 1
 | Rec No / Order No. | Initials / Del | Stock In / Out | Prod ID No. |  |  | Opening Balance | Stock In | Stock Out | Closing Balance |  | Opening Balance | Stock In | Stock Out | Closing Balance | Total Closing
0 |  |  |  |  |  | 001 | 1 |  |  | 1 |  | 3 |  |  | 3 | 4 |  |  |  | When the stock item (001) is removed from the store it needs to be taken first from the capex stock out column until that has been exhausted and then the opex stock out column until that stock has been exhausted, no stock can show minus figures.                                                              I have set up the sheet so that when stock is delivered with a C prefix it is automatically put into capex stock, but a delivery number without the C prefix is put into opex stock. The two formulas to look at are in E2 and J2.
1 | 2221 | KM | 1 | 001 |  |  | 1 | 0 | 0 | 1 |  | 3 | 0 | -1 | 2 | 3
2 | 2222 | KM | 1 | 001 |  |  | 1 | 0 | 0 | 1 |  | 2 | 0 | -1 | 1 | 2
3 | 122578 | DEL | 1 | 001 |  |  | 1 | 1 | 0 | 2 |  | 1 | 0 | 0 | 1 | 3
4 | C1768 | DEL | 1 | 001 |  |  | 2 | 0 | 0 | 2 |  | 1 | 1 | 0 | 2 | 4
5 | 2223 | km | 1 | 001 |  |  | 2 | 0 | 0 | 2 |  | 2 | 0 | -1 | 1 | 3
6 | 125579 | DEL | 1 | 001 |  |  | 2 | 1 | 0 | 3 |  | 1 | 0 | 0 | 1 | 4
7 | C1768 | DEL | 1 | 001 |  |  | 3 | 0 | 0 | 3 |  | 1 | 1 | 0 | 2 | 5
8 | 2224 | km | 1 | 001 |  |  | 3 | 0 | 0 | 3 |  | 2 | 0 | -1 | 1 | 4
9 | 2225 | km | 1 | 001 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | -1 | 0 | 3
10 | C1234 | DEL | 1 | 001 |  |  | 3 | 0 | 0 | 3 |  | 0 | 1 | 0 | 1 | 4
11 | 2221 | KM | 1 | 002 | A |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1 | 4
12 | 2222 | KM | 1 | 002 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1 | 4
13 | 122578 | DEL | 1 | 002 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
14 | C1768 | DEL | 1 | 002 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
15 | 2223 | km | 1 | 002 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
16 | 125579 | DEL | 1 | 002 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
17 | C1768 | DEL | 1 | 002 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
18 | 2224 | km | 1 | 002 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
19 | 2225 | km | 1 | 002 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
20 | C1234 | DEL | 1 | 002 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
21 | 2221 | KM | 1 | 003 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
22 | 2222 | KM | 1 | 003 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
23 | 122578 | DEL | 1 | 003 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
24 | C1768 | DEL | 1 | 003 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
25 | 2223 | km | 1 | 003 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
26 | 125579 | DEL | 1 | 003 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
27 | C1768 | DEL | 1 | 003 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
28 | 2224 | km | 1 | 003 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
29 | 2225 | km | 1 | 003 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
30 | C1234 | DEL | 1 | 003 |  |  | 3 | 0 | 0 | 3 |  | 1 | 0 | 0 | 1
 | Data Table
 | ID No. | opex open stock | opex stock in | opex stock out | Opex Running Stock |  | capex open stock | capex stock in | capex stock out | Capex Running Stock | Total Running Stock
 |  | 1 | 2 | 0 | 3 | 0 | 3 | 3 | 5 | 1 | 4
1 | 001 | 1 | 2 | 0 | 3 | 0 | 3 | 3 | 5 | 1 | 4
2 | 002 | 2 | 2 | 1 | 3 | 0 | 2 | 3 | 4 | 1 | 4
3 | 003 | 3 | 2 | 2 | 3 | 0 | 1 | 3 | 3 | 1 | 4