thewiseguy
Well-known Member
- Joined
- May 23, 2005
- Messages
- 1,013
- Office Version
- 365
- Platform
- Windows
Hello all,
I am looking for help creating a single formula that will give me the SUM of multiple products that we offer. The sheet below is our Bill of Materials. This grabs the unique values from our input page and now I want to find the total quantity of products from our INPUT page, but they span over multiple columns. See the screenshot below the BOM page. (Please note, the current formula in column I will be removed, as the data is now coming from multiple hidden pages - I am trying to simplify).
This is our INPUT page.
The products from our BOM page can be found in columns: R, W, Y, AA, AC, AE, AG
The quantities from our BOM page can be found in columns: U, X, Z, AB, AD, AF, AH
Any help is appreciated as I hope I have been clear in what I am trying to accomplish!
I am looking for help creating a single formula that will give me the SUM of multiple products that we offer. The sheet below is our Bill of Materials. This grabs the unique values from our input page and now I want to find the total quantity of products from our INPUT page, but they span over multiple columns. See the screenshot below the BOM page. (Please note, the current formula in column I will be removed, as the data is now coming from multiple hidden pages - I am trying to simplify).
v2024.38-1.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
B | I | J | |||||||||
3 | Mfg/Item | Quantity | Unit Cost | ||||||||
4 | HALCO 10.5W LED 4ft T8 Lamp [4K] | $3.75 | |||||||||
5 | HALCO 8in LED Downlight [12W-MCCT] | 8 | $39.00 | ||||||||
6 | HONYA LED 2ft 220W Linear Highbay [160W-MCCT] | 3 | $73.00 | ||||||||
7 | MAXLITE 2X2 LED Flat Panel [18W-MCCT] | 361 | $25.81 | ||||||||
8 | MAXLITE 2X2 LED Flat Panel [36W-MCCT] | 3 | $25.81 | ||||||||
9 | MAXLITE 2x2 LED Troffer Retrofit [25W-MCCT] | 1 | $50.00 | ||||||||
10 | MAXLITE 2X4 LED Flat Panel [27W-MCCT] | 66 | $36.56 | ||||||||
11 | MAXLITE 8ft LED Value Strip Fixture [55W-MCCT] | 84 | $53.76 | ||||||||
12 | MESTER 205W LED Linear Highbay [150W-MCCT-480V] | 12 | $131.64 | ||||||||
13 | LM Steel Bullhorn with 2 Arms at 180 Degrees | 4 | $1,500.00 | ||||||||
14 | LM Tenon Arm for Wall Mounting | 2 | $10.00 | ||||||||
15 | ENVISIONLED Daylight & Bi-Level Motion Sensor | 28 | $0.67 | ||||||||
16 | TBD 1/2-EMT Conduit 1/2-Inch x 10' | 15 | $500.00 | ||||||||
17 | TBD U Channel Beam Clamp for Kindorf | 400 | $1,000.00 | ||||||||
18 | ROSELLE Roll Off Dumpsters – 30 Yard | 2 | $2,000.00 | ||||||||
19 | EASTERNHIGHREACH 19 ft. Scissor Lift, Electric, Narrow [M] | 1 | $3.90 | ||||||||
20 | EASTERNHIGHREACH 24-26 ft. Scissor Lift, Electric, Wide [M] | 1 | $3.90 | ||||||||
21 | VEOLIA Supply 4' Lamp Box | 14 | |||||||||
22 | VEOLIA Supply U-Tube Box | 38 | |||||||||
BOM |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B4:B22 | B4 | =LET(v,VSTACK('ECM (BOM)'!B4#,'Accessories (BOM)'!B4#,'Controls (BOM)'!B4#,'Materials (BOM)'!B4#,'Rental Equipment (BOM)'!B4:B26, 'Recycle (BOM)'!B43#),FILTER(v,v<>"")) |
I5:I22 | I5 | =IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(B5,ECM_BOM_Table,7,0),VLOOKUP(B5,CONTROLS_BOM_TABLE,7,0)),VLOOKUP(B5,ACCESSORIES_BOM_TABLE,7,0)),VLOOKUP(B5,MATERIALS_BOM_TABLE,7,0)),VLOOKUP(B5,RENTAL_EQUIPMENT_BOM_TABLE,7,0)),VLOOKUP(B5,Recycle_BOM_Table,7,0)),"") |
Dynamic array formulas. |
This is our INPUT page.
The products from our BOM page can be found in columns: R, W, Y, AA, AC, AE, AG
The quantities from our BOM page can be found in columns: U, X, Z, AB, AD, AF, AH
Any help is appreciated as I hope I have been clear in what I am trying to accomplish!
v2024.38-1.xlsm | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | |||
2 | ECM | Accessory 1 | Accessory 2 | Control | Materials 1 | Materials 2 | Add. Labor | ||||||||||||||
3 | Product | ECM | ECM SubCategory | Override | Override Qty | ECM QTY | Labor Qty | Accessory Name | QTY | Accessory Name | QTY | Control Name | QTY | Material Name | QTY | Material Name | QTY | Add. Labor | QTY | ||
4 | Fixture | Flat Panel | MAXLITE 2X2 LED Flat Panel [18W-MCCT] | 2 | 2 | LM Steel Bullhorn with 2 Arms at 180 Degrees | 2 | LM Tenon Arm for Wall Mounting | 2 | ENVISIONLED Daylight & Bi-Level Motion Sensor | 14 | TBD U Channel Beam Clamp for Kindorf | 200 | TBD 1/2-EMT Conduit 1/2-Inch x 10' | 15 | Re-wire existing | 2 | ||||
Input |
Cell Formulas | ||
---|---|---|
Range | Formula | |
U4 | U4 | =IFERROR(IFS(R4="EMA No Measure", 0, S4="Yes", T4, P4="Lamp", Helper!G4 * J4, TRUE, J4),"") |
V4 | V4 | =IFS(R4="EMA No Measure", 0, S4="Yes", T4,S4="",J4) |