Spreadsheet: 746266d1630599219-formula-to-calculate-a-value-per-unique-part-with-multiple-warehouse-locations-excel-forum-question_090221-unallocated-inventory
Sheet: Sheet1

Item # | Item Desc. | Customer | Cat | Act | Location | Qty on Hand | UOM | Unit Cost | Value | Qty Needed | Value Needed | Balance | Revised Value | Qty Ordered | Lead Time | Order Value | [NAME] | Filter | QOO2 | Unallocated
C051 | [NAME] | ABC Company | PB | A | 1 | 23282 | EA | 0.10665 |  | 1999102.28 |  |  |  | 2032800 | 56 |  |  | NO
C051 | [NAME] | ABC Company | PB | A | 1MR | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C051 | [NAME] | ABC Company | PB | A | 1R | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C051 | [NAME] | ABC Company | PB | A | 2 | 254100 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | YES
C051 | [NAME] | ABC Company | PB | A | 2MR | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C051 | [NAME] | ABC Company | PB | A | 2R | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C051 | [NAME] | ABC Company | PB | A | OBS | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C051 | [NAME] | ABC Company | PB | A | PLT | 0 | EA | 0.10665 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | 1 | 5560 | EA | 0.2627 |  | 219313.78 |  |  |  | 0 | 84 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | 1MR | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | 1R | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | 2 | 68000 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | YES
C1036 | Bottle - Blue | XYZ Company | PB | A | 2MR | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | 2R | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | OBS | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1036 | Bottle - Blue | XYZ Company | PB | A | PLT | 0 | EA | 0.2627 |  | 0 |  |  |  | 0 | 84 |  |  | NO
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 1 | 12733 | EA | 0.10331 |  | 850586.36 |  |  |  | 310000 | 56 |  |  | NO
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 1MR | 0 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 1R | 0 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 2 | 207500 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | YES
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 2MR | 0 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C1077 | Bottle - Amber | LMN, Inc. | PB | A | 2R | 0 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | NO
C1077 | Bottle - Amber | LMN, Inc. | PB | A | PLT | 0 | EA | 0.10331 |  | 0 |  |  |  | 0 | 56 |  |  | NO
Old formula in Column "U" previously returned the correct Unallocated $ amounts per Item #
This was when all QOH was represented in only Location 1 (warehouse)
Now there are also active QOH in Location 2 (warehouse)
Quantities Needed are still exclusively shown in Location 1
In the above example:
Item # C051 should return $0.00 in Column "U" (as the Qty needed 1,999,102 exceeds the Loc1+Loc2 QOH of 277,382)
Item # C1036 should return $0.00 in Column "U"  (as the Qty needed 219,314 exceeds the Loc1+Loc2 QOH of 73,560)
Item # C1077 should return $0.00 in Column "U"  (as the Qty needed 850,586 exceeds the Loc1+Loc2 QOH of 220,233)