thewiseguy
Well-known Member
- Joined
- May 23, 2005
- Messages
- 1,015
- Office Version
- 365
- Platform
- Windows
Can someone help identify why this formula is NOT returning a value of 14?
=SUMPRODUCT(('Recycle (BOM)'!$B$43:$B$46=$B$20)*'Recycle (BOM)'!$H$43:$H$46)
Here is 'Recycle (BOM)' tab:
=SUMPRODUCT(('Recycle (BOM)'!$B$43:$B$46=$B$20)*'Recycle (BOM)'!$H$43:$H$46)
v2024.38-1.xlsm | |||
---|---|---|---|
B | |||
20 | VEOLIA Supply 4' Lamp Box | ||
BOM |
Here is 'Recycle (BOM)' tab:
v2024.38-1.xlsm | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | T | |||||||||
42 | Mfg/Item | Item | Product Type | Product Subgroup | Manufacturer | Part # | QTY Materials | Unit Cost ($) Materials | Extended Cost Materials | Margin % Materials | Unit Price Materials | Extended Price Materials | Notes | ||||||||
43 | VEOLIA Supply 4' Lamp Box | Supply 4' Lamp Box | Recycling | Recycle | VEOLIA | SUPPLY-002 | 14 | $4.14 | $57.96 | 0.00% | $4.14 | $57.96 | |||||||||
44 | VEOLIA Supply U-Tube Box | Supply U-Tube Box | Recycling | Recycle | VEOLIA | SUPPLY-005 | 38 | $4.14 | $157.32 | 0.00% | $4.14 | $157.32 | |||||||||
45 | 0 | 0 | 0 | 0 | $0.00 | ||||||||||||||||
46 | 0 | 0 | 0 | 0 | $0.00 | ||||||||||||||||
Recycle (BOM) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B43:B44 | B43 | =SORT(UNIQUE(FILTER('Helper 2'!F6:F505,('Helper 2'!F6:F505<>"")*('Helper 2'!G6:G505<>0)))) |
C43:C46 | C43 | =IFERROR(RIGHT(B43,LEN(B43)-FIND(" ",B43)),"") |
D43:D46 | D43 | =IFERROR((XLOOKUP(B43,Tbl_Area[MaskedPartNumber],Tbl_Area[Product_Type],XLOOKUP(B43,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[Product_Type],XLOOKUP(B43,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[Product_Type],XLOOKUP(B43,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[Product_Type],XLOOKUP(B43,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[Product_Type],XLOOKUP(B43,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[Product_Type],XLOOKUP(B43,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[Product_Type],XLOOKUP(B43,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[Product_Type],XLOOKUP(B43,Tbl_Linear[MaskedPartNumber],Tbl_Linear[Product_Type],XLOOKUP(B43,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[Product_Type],XLOOKUP(B43,Tbl_Strip[MaskedPartNumber],Tbl_Strip[Product_Type],XLOOKUP(B43,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[Product_Type],XLOOKUP(B43,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[Product_Type],XLOOKUP(B43,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[Product_Type],XLOOKUP(B43,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[Product_Type],XLOOKUP(B43,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[Product_Type],XLOOKUP(B43,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[Product_Type],XLOOKUP(B43,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[Product_Type],XLOOKUP(B43,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[Product_Type],XLOOKUP(B43,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[Product_Type],XLOOKUP(B43,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[Product_Type],XLOOKUP(B43,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[Product_Type],XLOOKUP(B43,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[Product_Type],XLOOKUP(B43,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[Product_Type],XLOOKUP(B43,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[Product_Type],XLOOKUP(B43,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Product_Type],XLOOKUP(B43,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Product_Type],XLOOKUP(B43,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Product_Type],XLOOKUP(B43,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Product_Type],XLOOKUP(B43,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[Product_Type]))))))))))))))))))))))))))))))),"") |
E43:E46 | E43 | =IFERROR((XLOOKUP(B43,Tbl_Area[MaskedPartNumber],Tbl_Area[Product_Subgroup],XLOOKUP(B43,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[Product_Subgroup],XLOOKUP(B43,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[Product_Subgroup],XLOOKUP(B43,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[Product_Subgroup],XLOOKUP(B43,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[Product_Subgroup],XLOOKUP(B43,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[Product_Subgroup],XLOOKUP(B43,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[Product_Subgroup],XLOOKUP(B43,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[Product_Subgroup],XLOOKUP(B43,Tbl_Linear[MaskedPartNumber],Tbl_Linear[Product_Subgroup],XLOOKUP(B43,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[Product_Subgroup],XLOOKUP(B43,Tbl_Strip[MaskedPartNumber],Tbl_Strip[Product_Subgroup],XLOOKUP(B43,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[Product_Subgroup],XLOOKUP(B43,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[Product_Subgroup],XLOOKUP(B43,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[Product_Subgroup],XLOOKUP(B43,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[Product_Subgroup],XLOOKUP(B43,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[Product_Subgroup],XLOOKUP(B43,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[Product_Subgroup],XLOOKUP(B43,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[Product_Subgroup],XLOOKUP(B43,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[Product_Subgroup],XLOOKUP(B43,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[Product_Subgroup],XLOOKUP(B43,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[Product_Subgroup],XLOOKUP(B43,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[Product_Subgroup],XLOOKUP(B43,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[Product_Subgroup],XLOOKUP(B43,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[Product_Subgroup],XLOOKUP(B43,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[Product_Subgroup],XLOOKUP(B43,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Product_Subgroup],XLOOKUP(B43,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Product_Subgroup],XLOOKUP(B43,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Product_Subgroup],XLOOKUP(B43,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Product_Subgroup],XLOOKUP(B43,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[Product_Subgroup]))))))))))))))))))))))))))))))),"") |
F43:F46 | F43 | =IFERROR((XLOOKUP(B43,Tbl_Area[MaskedPartNumber],Tbl_Area[Brand],XLOOKUP(B43,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[Brand],XLOOKUP(B43,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[Brand],XLOOKUP(B43,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[Brand],XLOOKUP(B43,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[Brand],XLOOKUP(B43,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[Brand],XLOOKUP(B43,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[Brand],XLOOKUP(B43,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[Brand],XLOOKUP(B43,Tbl_Linear[MaskedPartNumber],Tbl_Linear[Brand],XLOOKUP(B43,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[Brand],XLOOKUP(B43,Tbl_Strip[MaskedPartNumber],Tbl_Strip[Brand],XLOOKUP(B43,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[Brand],XLOOKUP(B43,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[Brand],XLOOKUP(B43,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[Brand],XLOOKUP(B43,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[Brand],XLOOKUP(B43,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[Brand],XLOOKUP(B43,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[Brand],XLOOKUP(B43,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[Brand],XLOOKUP(B43,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[Brand],XLOOKUP(B43,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[Brand],XLOOKUP(B43,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[Brand],XLOOKUP(B43,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[Brand],XLOOKUP(B43,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[Brand],XLOOKUP(B43,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[Brand],XLOOKUP(B43,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[Brand],XLOOKUP(B43,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Brand],XLOOKUP(B43,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Brand],XLOOKUP(B43,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Brand],XLOOKUP(B43,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Brand],XLOOKUP(B43,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[Brand]))))))))))))))))))))))))))))))),"") |
G43:G46 | G43 | =IFERROR((XLOOKUP(B43,Tbl_Area[MaskedPartNumber],Tbl_Area[PartNumber],XLOOKUP(B43,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[PartNumber],XLOOKUP(B43,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[PartNumber],XLOOKUP(B43,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[PartNumber],XLOOKUP(B43,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[PartNumber],XLOOKUP(B43,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[PartNumber],XLOOKUP(B43,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[PartNumber],XLOOKUP(B43,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[PartNumber],XLOOKUP(B43,Tbl_Linear[MaskedPartNumber],Tbl_Linear[PartNumber],XLOOKUP(B43,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[PartNumber],XLOOKUP(B43,Tbl_Strip[MaskedPartNumber],Tbl_Strip[PartNumber],XLOOKUP(B43,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[PartNumber],XLOOKUP(B43,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[PartNumber],XLOOKUP(B43,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[PartNumber],XLOOKUP(B43,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[PartNumber],XLOOKUP(B43,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[PartNumber],XLOOKUP(B43,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[PartNumber],XLOOKUP(B43,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[PartNumber],XLOOKUP(B43,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[PartNumber],XLOOKUP(B43,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[PartNumber],XLOOKUP(B43,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[PartNumber],XLOOKUP(B43,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[PartNumber],XLOOKUP(B43,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[PartNumber],XLOOKUP(B43,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[PartNumber],XLOOKUP(B43,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[PartNumber],XLOOKUP(B43,Tbl_Materials[MaskedPartNumber],Tbl_Materials[PartNumber],XLOOKUP(B43,Tbl_Rental[MaskedPartNumber],Tbl_Rental[PartNumber],XLOOKUP(B43,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[PartNumber],XLOOKUP(B43,Tbl_Controls[MaskedPartNumber],Tbl_Controls[PartNumber],XLOOKUP(B43,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[PartNumber]))))))))))))))))))))))))))))))),"") |
H43:H46 | H43 | =IFERROR(ROUNDUP(IFERROR(IF(SUMPRODUCT(--('Helper 2'!$F$6:$F$505=B43),'Helper 2'!$G$6:$G$505)=0,"",SUMPRODUCT(--('Helper 2'!$F$6:$F$505=B43),'Helper 2'!$G$6:$G$505)),0),0),"") |
I43:I46 | I43 | =XLOOKUP(B43,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[Cost],0) |
J43:J46 | J43 | =IFERROR(H43*I43,"") |
K43:K46 | K43 | =IF(B43<>"", 'Project Items '!$E$16, "") |
L43:L46 | L43 | =IFERROR(((I43/(1-K43))-I43)+I43,"") |
M43:M46 | M43 | =IFERROR(H43*L43,"") |
Dynamic array formulas. |