Hello,
I've been trying various ways of making formulas work to make a "cost roll" out of a Bill of Materials. I keep getting stuck... (I've tried some various other formulas but the one below I've kinda landed on for now)
(apologies - I have to hide columns to get this in here, but other columns aren't data I'm looking at for this anyways)
Column AF = Standard Price - this is the cost of a single quantity of the PN
Column AG = Extended Price - this is the standard price (Column AF) times the quantity needed (column J) - this is where the rollup should be.
Column D Formula = VALUE(RIGHT(C3))
- This column is merely entered in as a helper column because the actual BOM uses "weird" numbering in Column B, which I would actually be using.
Column O Formula = LOOKUP(VALUE(RIGHT(C3))-1,VALUE(RIGHT($C$3:C3)),$F$3:F3,$F$3,0,-1)
- This is required on the sheet (for data entry later) and I'm using it as a "helper" for the actual formula
Column AM formula (this will ultimately reside in Column AG - I just moved it off to the side so I can read it easier) = IF(S3="E=MAKE",SUM(CHOOSECOLS(FILTER(D3:$AM$3005,(D3:$D$3005=D3+1)*(O3:$O$3005=F3),""),36)),AF3*J3)
- This is the one I'm manipulating. I've gone through lots of various different versions of this and can't seem to land a solution which actually works.
I've been trying various ways of making formulas work to make a "cost roll" out of a Bill of Materials. I keep getting stuck... (I've tried some various other formulas but the one below I've kinda landed on for now)
(apologies - I have to hide columns to get this in here, but other columns aren't data I'm looking at for this anyways)
Column AF = Standard Price - this is the cost of a single quantity of the PN
Column AG = Extended Price - this is the standard price (Column AF) times the quantity needed (column J) - this is where the rollup should be.
Column D Formula = VALUE(RIGHT(C3))
- This column is merely entered in as a helper column because the actual BOM uses "weird" numbering in Column B, which I would actually be using.
Column O Formula = LOOKUP(VALUE(RIGHT(C3))-1,VALUE(RIGHT($C$3:C3)),$F$3:F3,$F$3,0,-1)
- This is required on the sheet (for data entry later) and I'm using it as a "helper" for the actual formula
Column AM formula (this will ultimately reside in Column AG - I just moved it off to the side so I can read it easier) = IF(S3="E=MAKE",SUM(CHOOSECOLS(FILTER(D3:$AM$3005,(D3:$D$3005=D3+1)*(O3:$O$3005=F3),""),36)),AF3*J3)
- This is the one I'm manipulating. I've gone through lots of various different versions of this and can't seem to land a solution which actually works.
| B | C | D | E | F | J | K | O | S | AF | AG | AM | |
| Orig Seq | Level No | BOM Item | Component | QTY | UN | Sort String | Proc Type | Std Price | EXT Price | |||
1 | 0 | 0 | 1E6801500-00 | 0.000 | 1E6801500-00 | E=MAKE | $0.00 | 1304612.02 | ||||
2 | 0.1 | 1 | 1 | 1282-128-01 | 1 | EA | 1E6801500-00 | E=MAKE | $1,383,929.33 | 1171915.36 | ||
3 | ..2 | 2 | 2850 | 1282-694-01 | 1 | EA | 1282-128-01 | E=MAKE | $243,261.18 | 257867.744 | ||
4 | ...3 | 3 | 1410 | 1281-052-01 | 1 | EA | 1282-694-01 | F | $26,919.42 | $26,919.42 | 26919.42 | |
5 | ...3 | 3 | 1420 | 1285-490-01 | 1 | EA | 1282-694-01 | E=MAKE | $34,453.17 | 38680.04 | ||
6 | ....4 | 4 | 10 | 1295-812-01 | 3 | EA | 1285-490-01 | F | $57.50 | $172.50 | 172.5 | |
7 | ....4 | 4 | 20 | 1296-405-01 | 2 | EA | 1285-490-01 | F | $190.82 | $381.64 | 381.64 | |
8 | ....4 | 4 | 30 | 1230-034-01 | 1 | EA | 1285-490-01 | F | $3,802.84 | $3,802.84 | 3802.84 | |
9 | ....4 | 4 | 240 | 1283-053-01 | 1 | EA | 1285-490-01 | F | $16,141.75 | $16,141.75 | 16141.75 | |
10 | ....4 | 4 | 250 | 1285-489-01 | 1 | EA | 1285-490-01 | E=MAKE | $72.01 | 68.99 | ||
11 | .....5 | 5 | 10 | 1230-020-01 | 1 | EA | 1285-489-01 | F | $33.45 | $33.45 | 33.45 | |
12 | .....5 | 5 | 20 | 1051-460-01 | 1 | EA | 1285-489-01 | F | $0.20 | $0.20 | 0.2 | |
13 | .....5 | 5 | 30 | 1051-461-01 | 1 | EA | 1285-489-01 | F | $0.20 | $0.20 | 0.2 | |
14 | .....5 | 5 | 40 | 9191-580032 | 1 | EA | 1285-489-01 | F | $2.79 | $2.79 | 2.79 | |
15 | .....5 | 5 | 50 | 9191-580031 | 1 | EA | 1285-489-01 | F | $3.08 | $3.08 | 3.08 | |
16 | .....5 | 5 | 60 | 1136-450-01 | 1 | EA | 1285-489-01 | F | $2.01 | $2.01 | 2.01 | |
17 | .....5 | 5 | 70 | 1136-449-01 | 1 | EA | 1285-489-01 | F | $1.12 | $1.12 | 1.12 | |
18 | .....5 | 5 | 80 | 1230-028-01 | 1 | EA | 1285-489-01 | F | $26.14 | $26.14 | 26.14 | |
19 | ....4 | 4 | 260 | 1285-488-01 | 2 | EA | 1285-490-01 | E=MAKE | $78.32 | 150.6 | ||
20 | .....5 | 5 | 10 | 1230-020-01 | 2 | EA | 1285-488-01 | F | $33.45 | $66.90 | 66.9 | |
21 | .....5 | 5 | 20 | 1051-460-01 | 2 | EA | 1285-488-01 | F | $0.20 | $0.40 | 0.4 | |
22 | .....5 | 5 | 30 | 1051-461-01 | 2 | EA | 1285-488-01 | F | $0.20 | $0.40 | 0.4 | |
23 | .....5 | 5 | 40 | 9191-580032 | 2 | EA | 1285-488-01 | F | $2.79 | $5.58 | 5.58 | |
24 | .....5 | 5 | 50 | 9191-580031 | 2 | EA | 1285-488-01 | F | $3.08 | $6.16 | 6.16 | |
25 | .....5 | 5 | 60 | 1230-030-01 | 2 | EA | 1285-488-01 | F | $9.44 | $18.88 | 18.88 | |
26 | .....5 | 5 | 70 | 1230-028-01 | 2 | EA | 1285-488-01 | F | $26.14 | $52.28 | 52.28 | |
27 | ....4 | 4 | 270 | 1230-031-01 | 9 | EA | 1285-490-01 | F | $52.80 | $475.20 | 475.2 | |
28 | ....4 | 4 | 280 | 1246-786-01 | 24 | EA | 1285-490-01 | F | $27.10 | $650.40 | 650.4 | |
29 | ....4 | 4 | 290 | 1230-049-01 | 4 | EA | 1285-490-01 | E=MAKE | $247.34 | 884.48 | ||
30 | .....5 | 5 | 10 | 1301-866-01 | 4 | EA | 1230-049-01 | F | $75.00 | $300.00 | 300 | |
31 | .....5 | 5 | 20 | 73055-01411 | 4 | EA | 1230-049-01 | F | $13.30 | $53.20 | 53.2 | |
32 | .....5 | 5 | 30 | 1230-048-01 | 4 | EA | 1230-049-01 | F | $40.60 | $162.40 | 162.4 | |
33 | .....5 | 5 | 40 | 95700-04010 | 24 | EA | 1230-049-01 | Bulk | $0.07 | $1.68 | 1.68 | |
34 | .....5 | 5 | 50 | 1226-430-01 | 4 | EA | 1230-049-01 | F | $91.80 | $367.20 | 367.2 |
