SUMIFS using criteria from a filtered table

J

JasonC79

New Member
Joined
Mar 21, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Good evening Excel gurus. I'm a self taught individual that is posting for the first time, so please forgive me if I'm not following protocol in any way. I have attached 2 stripped down XL2BB tables of my Office365 Excel file for discussion. I am trying to add the number of machine hours in column B based on the criteria in column D, and load the summations to the corresponding cells in column E. The second table, actually on a different tab, has the indirect union information that connects the part numbers to the workcenters, columns A and E. I'm thinking it has to be a SUMIFS situation, but I can't seem to figure out how to develop the union criterion that correlates the workcenters to the associated part numbers, by workcenter, to sum the machine hours. I would greatly appreciate some guidance. I'm pulling my hair out on this and I don't have much more to give up.

Albion JPH Rev 4.xlsx
ABCDE
1Part NumberTotal Hours/Machine/WeekWorkcentersHours/Week/Workcenter
210103353197.1CNC 105 RH 6K Knuckle
310103353CX356.1CNC 108 RH 6K Knuckle
410103355222.8CNC 121 RH 6K Knuckle
510103355CX296.5CNC 291 RH 6K Knuckle
610103357228.7CNC 114 RH 6K Knuckle
710103357CD306.8CNC 285 RH 6K Knuckle
820049170.0CNC 104 RH 6K Knuckle
910103358217.5CNC 322 RH 6K Knuckle
1010103358CD330.0CNC 26 RH 6K Knuckle
1120049180.0CNC 118 LH 6K Knuckle
1210037203105.9CNC 292 LH 6K Knuckle
1310037207105.9CNC 120 LH 6K Knuckle
14001-0408-04W35.7CNC 106 LH 6K Knuckle
15001-0408-05W0.0CNC 303 LH 6K Knuckle
16001-0408-06W0.0CNC 310 LH 6K Knuckle
17001-0924-000.0CNC 158 LH 6K Knuckle
18001-0924-019.1CNC 290 LH 6K Knuckle
19001-0924-028.9CNC 25 LH 6K Knuckle
20001-0924-0317.0CNC 301 RH 7K Knuckle
21501-1234-010.0CNC 302 RH 7K Knuckle
22501-1234-023.8CNC 123 7K RH
23501-1234-035.6CNC 148 LH 7K Knuckle
24501-1234-040.0CNC 149 LH 7K Knuckle
25501-1234-058.0CNC 123 LH 7K
26501-1234-080.0CNC 345
2768400221AA38.6Copeland 2D CNC 176
28H22407920.4Copeland 3D CNC 147 CNC 166
29H22408020.4Copeland K Body 3rd op CNC 368
30H2GC 5K651 AB118.3Winch Brkt CNC 276
31H2GC 5K652 AB120.2CNC 323 BE 288
32HXE6642238.8CD 4 Control Arm RH CNC 451 453
33L22288414.7CD 4 Control Arm RH CNC 295 449
34L22288550.9CD 4 Control Arm LH CNC 452 454
35PR3C-3C259-DA0.0CD 4 Control Arm LH CNC 450 455
36PR3C-3C259-EA0.0John Deere Diff Case CNC 348 CNC 349 CNC 350
37PR3C-3C260-DA0.0CNC209
38PR3C-3C260-EA0.0CNC 165 BE 288
39R34440020.3CNC 41 and CNC314
40R54246114.9CNC 284 and CNC 298 JD Front Control Support
41R55665628.4Honda RDX CNC- 150
42TR121895173.4Honda RDX CNC- 103
4351394TJB A040M154.6CNC 258 Dana Bearing Cap
4451393TJB A040M170.9CNC 308 Dana Bearing Cap/Web Pilot
451310150.6CNC 14 31X Bracket RH
461313242.0CNC 94 31X Bracket LH
4713998941.5CNC 318 Rear Carrier / S172
4813999041.5CNC 319 Rear Carrier / S172
492017707119.1CNC 113 Front Carrier
502017710105.1CNC 102 Front Carrier
511003541517.5CNC 317 Front Carrier
52100354165.9CNC 101 Front Carrier
53100354172.0CNC 261 Front Carrier
541003541934.1CNC 305 S172 Caps
551003542018.3CNC 320 Rear Carrier / S172
56100354219.9CNC 321 Rear Carrier / S172
571003542214.1CNC 372/304 TRX
581004439135.0CNC 460 BE 424
591004443435.0CNC 155 BE 314 BE 153 BE 424 BE 256
601004655369.3CNC 178
61100477615.4JD CNC 130
62100477640.0J.D Twin Spindle BE 23
63100509220.0CNC 306 John Deere
641005210334.8CNC 156 BE 128
6510115487177.6John Deere CNC358
66DZ10675328.2John Deere VTL Cell 1
67DZ10754925.7CNC 210 John Deere Misc.
68H2047756.3John Deere VTL Cell 2 CNC 268
69H2388070.0J.D Pulleys CNC 197
70HXE1040022.7CNC 44
71HXE1660030.7
72HXE20534HX134.6
73HXE7196918.5
74HXE98995HX151.1
75R1307533.7
76R13586620.7
77R1559520.0
78R16808919.6
79R23924614.2
80R2442850.0
81R2442860.0
82R2562725.1
83R2870970.0
84R3038895.9
85R3058780.0
86R3058809.5
87R5383920.0
88R54103138.1
89R5414751.5
90R544537RX13.4
91R5463620.0
92R5463630.0
93R54636510.4
94R55883860.0
95R5590689.5
96R5596619.8
97R5650605.3
98R56575820.7
99R56575926.7
100R5695910.0
101TR116965RX110.4
102TR116968RX10.0
103YZ5028985.6
Sheet1


Albion JPH Rev 4.xlsx
ABCDE
1Part NoNamePart TypeMachine NumberWorkcenter
2131015 Web Pilot Web PilotCNC258CNC 258 Dana Bearing Cap
3131015 Web Pilot Web PilotCNC308CNC 308 Dana Bearing Cap/Web Pilot
4131324 Web Pilot Web PilotCNC308CNC 308 Dana Bearing Cap/Web Pilot
5139989 Bearing Cap Bearing CapCNC258CNC 258 Dana Bearing Cap
6139989 Bearing Cap Bearing CapCNC308CNC 308 Dana Bearing Cap/Web Pilot
7139990 Bearing Cap Bearing CapCNC258CNC 258 Dana Bearing Cap
8139990 Bearing Cap Bearing CapCNC308CNC 308 Dana Bearing Cap/Web Pilot
9201770731X Bracket RHBRACKETCNC14CNC 14 31X Bracket RH
10201771031X Bracket LHBRACKETCNC94CNC 94 31X Bracket LH
1110035415Rear CarrierCARRIERCNC318CNC 318 Rear Carrier / S172
1210035415Rear CarrierCARRIERCNC319CNC 319 Rear Carrier / S172
1310035416Rear CarrierCARRIERCNC318CNC 318 Rear Carrier / S172
1410035416Rear CarrierCARRIERCNC319CNC 319 Rear Carrier / S172
1510035417Rear CarrierCARRIERCNC318CNC 318 Rear Carrier / S172
1610035417Rear CarrierCARRIERCNC319CNC 319 Rear Carrier / S172
1710035419Rear CarrierCARRIERCNC318CNC 318 Rear Carrier / S172
1810035419Rear CarrierCARRIERCNC319CNC 319 Rear Carrier / S172
1910035420Front CarrierCARRIERCNC113CNC 113 Front Carrier
2010035420Front CarrierCARRIERCNC102CNC 102 Front Carrier
2110035420Front CarrierCARRIERCNC317CNC 317 Front Carrier
2210035420Front CarrierCARRIERCNC101CNC 101 Front Carrier
2310035421Front CarrierCARRIERCNC113CNC 113 Front Carrier
2410035421Front CarrierCARRIERCNC102CNC 102 Front Carrier
2510035421Front CarrierCARRIERCNC317CNC 317 Front Carrier
2610035421Front CarrierCARRIERCNC101CNC 101 Front Carrier
2710035422Front CarrierCARRIERCNC261CNC 261 Front Carrier
2810044391S172 Bearing CapBEARING CAPCNC305CNC 305 S172 Caps
2910044434S172 Bearing CapBEARING CAPCNC305CNC 305 S172 Caps
3010046553S172 Carrier Diff LockCARRIERCNC320CNC 320 Rear Carrier / S172
3110046553S172 Carrier Diff LockCARRIERCNC321CNC 321 Rear Carrier / S172
3210047761S175 CarrierCARRIERCNC320CNC 320 Rear Carrier / S172
3310047764S175 Bearing CapBEARING CAPCNC305CNC 305 S172 Caps
3410050922S175 Bearing CapBEARING CAPCNC305CNC 305 S172 Caps
3510052103S172 Carrier Non DiffCARRIERCNC320CNC 320 Rear Carrier / S172
3610052103S172 Carrier Non DiffCARRIERCNC321CNC 321 Rear Carrier / S172
3710115487FCA TRX CarrierCARRIERCNC372CNC 372/304 TRX
3810115487FCA TRX CarrierCARRIERCNC304CNC 372/304 TRX
39DZ106753JD Oil Filter ManifoldOTHER/NON-AUTOCNC460CNC 460 BE 424
40DZ106753JD Oil Filter ManifoldOTHER/NON-AUTOCNC155CNC 155 BE 314 BE 153 BE 424 BE 256
41DZ106753JD Oil Filter ManifoldOTHER/NON-AUTOCNC178CNC 178
42DZ107549Thermostat CoverOTHER/NON-AUTOCNC130JD CNC 130
43DZ107549Thermostat CoverOTHER/NON-AUTOCNC178CNC 178
44H204775HubOTHER/NON-AUTOCNC197J.D Twin Spindle BE 23
45H238807Fan BracketBRACKETCNC306CNC 306 John Deere
46HXE104002Spreader Hub PumpOTHER/NON-AUTOCNC197J.D Twin Spindle BE 23
47HXE16600JD BracketOTHER/NON-AUTOCNC460CNC 460 BE 424
48HXE16600JD BracketOTHER/NON-AUTOCNC155CNC 155 BE 314 BE 153 BE 424 BE 256
49HXE16600JD BracketOTHER/NON-AUTOCNC156CNC 156 BE 128
50HXE16600JD BracketOTHER/NON-AUTOCNC178CNC 178
51HXE16600JD BracketOTHER/NON-AUTOCNC358John Deere CNC358
52HXE20534HX1Auger RingOTHER/NON-AUTOCNC188John Deere VTL Cell 1
53HXE71969Front Drive SupportOTHER/NON-AUTOCNC210CNC 210 John Deere Misc.
54HXE98995HX1Chopper Bracket MountOTHER/NON-AUTOCNC306CNC 306 John Deere
55R130753Housing CoverOTHER/NON-AUTOCNC197J.D Twin Spindle BE 23
56R135866Hub WheelOTHER/NON-AUTOCNC288John Deere VTL Cell 2 CNC 268
57R135866Hub WheelOTHER/NON-AUTOCNC188John Deere VTL Cell 1
58R155952JD PlateOTHER/NON-AUTOCNC197J.D Pulleys CNC 197
59R168089Hub WheelOTHER/NON-AUTOCNC288John Deere VTL Cell 2 CNC 268
60R168089Hub WheelOTHER/NON-AUTOCNC188John Deere VTL Cell 1
61R239246Hub WheelOTHER/NON-AUTOCNC288John Deere VTL Cell 2 CNC 268
62R244285John Deere PulleyOTHER/NON-AUTOCNC197J.D Twin Spindle BE 23
63R244286HubOTHER/NON-AUTOCNC197J.D Twin Spindle BE 23
64R256272PistonOTHER/NON-AUTOCNC197J.D Twin Spindle BE 23
65R287097Housing CoverOTHER/NON-AUTOCNC197J.D Twin Spindle BE 23
66R303889PlateOTHER/NON-AUTOCNC188John Deere VTL Cell 1
67R305878PistonOTHER/NON-AUTOCNC188John Deere VTL Cell 1
68R305880PistonOTHER/NON-AUTOCNC188John Deere VTL Cell 1
69R538392Rear CoverOTHER/NON-AUTOCNC197J.D Twin Spindle BE 23
70R541031JD ManifoldOTHER/NON-AUTOCNC155CNC 155 BE 314 BE 153 BE 424 BE 256
71R541031JD ManifoldOTHER/NON-AUTOCNC156CNC 156 BE 128
72R541031JD ManifoldOTHER/NON-AUTOCNC178CNC 178
73R541475Front Axle SupportOTHER/NON-AUTOCNC210CNC 210 John Deere Misc.
74R544537RX1Fender SupportOTHER/NON-AUTOCNC210CNC 210 John Deere Misc.
75R546362JD FlangeOTHER/NON-AUTOCNC460CNC 460 BE 424
76R546362JD FlangeOTHER/NON-AUTOCNC155CNC 155 BE 314 BE 153 BE 424 BE 256
77R546362JD FlangeOTHER/NON-AUTOCNC178CNC 178
78R546363JD FlangeOTHER/NON-AUTOCNC460CNC 460 BE 424
79R546363JD FlangeOTHER/NON-AUTOCNC155CNC 155 BE 314 BE 153 BE 424 BE 256
80R546363JD FlangeOTHER/NON-AUTOCNC156CNC 156 BE 128
81R546363JD FlangeOTHER/NON-AUTOCNC178CNC 178
82R546365JD FlangeOTHER/NON-AUTOCNC155CNC 155 BE 314 BE 153 BE 424 BE 256
83R546365JD FlangeOTHER/NON-AUTOCNC156CNC 156 BE 128
84R558838JD Scavenger PumpOTHER/NON-AUTOCNC460CNC 460 BE 424
85R558838JD Scavenger PumpOTHER/NON-AUTOCNC155CNC 155 BE 314 BE 153 BE 424 BE 256
86R559068JD Oil TubeOTHER/NON-AUTOCNC460CNC 460 BE 424
87R559068JD Oil TubeOTHER/NON-AUTOCNC156CNC 156 BE 128
88R559661Bearing RetainerOTHER/NON-AUTOCNC197J.D Twin Spindle BE 23
89R565060Hub WheelOTHER/NON-AUTOCNC288John Deere VTL Cell 2 CNC 268
90R565758Pump Inlet Oil TubeOTHER/NON-AUTOCNC460CNC 460 BE 424
91R565758Pump Inlet Oil TubeOTHER/NON-AUTOCNC156CNC 156 BE 128
92R565758Pump Inlet Oil TubeOTHER/NON-AUTOCNC178CNC 178
93R565759Pump Inlet Oil TubeOTHER/NON-AUTOCNC460CNC 460 BE 424
94R565759Pump Inlet Oil TubeOTHER/NON-AUTOCNC156CNC 156 BE 128
95R569591DDS Quil HousingOTHER/NON-AUTOCNC44CNC 44
96TR116965RX1Charge Supply ManifoldOTHER/NON-AUTOCNC130JD CNC 130
97TR116965RX1Charge Supply ManifoldOTHER/NON-AUTOCNC358John Deere CNC358
98TR116968RX1Charge Supply ManifoldOTHER/NON-AUTOCNC130JD CNC 130
99TR116968RX1Charge Supply ManifoldOTHER/NON-AUTOCNC358John Deere CNC358
100YZ502898Bearing Retainer W/O BearingOTHER/NON-AUTOCNC197J.D Twin Spindle BE 23
Plant 9 JPH
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Welcome to the MrExcel board!

Your thread title mentions a filtered table. Your description and XL2BB samples don't seem to have anything to do with filtering.
Can you clarify that situation?

In any case, I don't understand the requirement. Could you make up even smaller samples and ensure there is a correlation between the two tables, manually enter the required results for at least 2 or 3 of the rows and post those XL2BB sheets?
Also, explain in words how you got those manually entered results?
 
Upvote 0
Hi Peter, my apologies, and thank you for the response. It was late and I'm so close to the file I missed the obvious: no one knows it like I do. Anyway, I was describing what I thought needed to be done with the tables. Here is a smaller table as requested.

Albion JPH Rev 4.xlsx
ABCDEFGHIJKLM
1Capacity Review by Part Number Snippet
2Part NumberNameAverage Weekly Planned VolumeEngineered JPH Parts producedActual Machine HoursActual JPHJake JPH Adjusted (Manning Improv) Hours Required Per weekMachines Per Part NumberPart Numbers Per Complete CycleTotal Hours/Machine/WeekShifts/Week
310103353P558 6K Knuckle Right Hand51127.5129072574.55.05.2989.981123.716.9
410103353CXP558 6K Knuckle Right Hand7506.41048203.65.15.3141.511141.519.3
510103355P558 6K Knuckle Left Hand51127.5109902122.25.25.3958.481119.816.3
610103355CXP558 6K Knuckle Left Hand7506.41212249.34.95.0149.811149.820.4
710103357P558 7K Knuckle Right Hand16006.63743719.65.25.4298.621149.320.4
810103357CDP558 7K Knuckle Right Hand3243.3440189.42.32.4135.411135.418.5
92004917Big Brake 7K Knuckle Right Hand07.100.00.00.00.0210.00.0
1010103358P558 7K Knuckle Left Hand16006.63719718.45.25.3300.121150.020.5
1110103358CDP558 7K Knuckle Left Hand3243.3415194.12.12.2147.111147.120.1
122004918Big Brake 7K Knuckle Left Hand07.100.00.00.00.0210.00.0
1310037203Navistar Vista Knuckle RH2162.031288.32.02.0105.912105.97.2
1410037207Navistar Vista Knuckle LH2162.031288.32.02.0105.912105.97.2
15001-0408-04W2D Honed240.9119125.90.91.024.71124.73.4
16001-0408-05W2D Honed120.900.00.00.00.0110.00.0
17
18JPH SnippetCapacity Review by Work Center
19Part NoNamePart TypeMachine NumberWorkcenterPlantWorkcentersShifts/Week
2010103353P558 6K Knuckle Right HandKnuckleCNC105CNC 105 RH 6K Knuckle6CNC 105 RH 6K Knuckle16.9
2110103353P558 6K Knuckle Right HandKnuckleCNC108CNC 108 RH 6K Knuckle6CNC 108 RH 6K Knuckle16.9
2210103353P558 6K Knuckle Right HandKnuckleCNC121CNC 121 RH 6K Knuckle6CNC 121 RH 6K Knuckle16.9
2310103353P558 6K Knuckle Right HandKnuckleCNC291CNC 291 RH 6K Knuckle6CNC 291 RH 6K Knuckle16.9
2410103353P558 6K Knuckle Right HandKnuckleCNC114CNC 114 RH 6K Knuckle6CNC 114 RH 6K Knuckle16.9
2510103353P558 6K Knuckle Right HandKnuckleCNC285CNC 285 RH 6K Knuckle6CNC 285 RH 6K Knuckle16.9
2610103353P558 6K Knuckle Right HandKnuckleCNC104CNC 104 RH 6K Knuckle6CNC 104 RH 6K Knuckle16.9
2710103353P558 6K Knuckle Right HandKnuckleCNC322CNC 322 RH 6K Knuckle6CNC 322 RH 6K Knuckle16.9
2810103353CXP558 6K Knuckle Right HandKnuckleCNC26CNC 26 RH 6K Knuckle6CNC 26 RH 6K Knuckle19.3
2910103355P558 6K Knuckle Left HandKnuckleCNC118CNC 118 LH 6K Knuckle6CNC 118 LH 6K Knuckle16.3
3010103355P558 6K Knuckle Left HandKnuckleCNC292CNC 292 LH 6K Knuckle6CNC 292 LH 6K Knuckle16.3
3110103355P558 6K Knuckle Left HandKnuckleCNC120CNC 120 LH 6K Knuckle6CNC 120 LH 6K Knuckle16.3
3210103355P558 6K Knuckle Left HandKnuckleCNC106CNC 106 LH 6K Knuckle6CNC 106 LH 6K Knuckle16.3
3310103355P558 6K Knuckle Left HandKnuckleCNC303CNC 303 LH 6K Knuckle6CNC 303 LH 6K Knuckle16.3
3410103355P558 6K Knuckle Left HandKnuckleCNC310CNC 310 LH 6K Knuckle6CNC 310 LH 6K Knuckle16.3
3510103355P558 6K Knuckle Left HandKnuckleCNC158CNC 158 LH 6K Knuckle6CNC 158 LH 6K Knuckle16.3
3610103355P558 6K Knuckle Left HandKnuckleCNC290CNC 290 LH 6K Knuckle6CNC 290 LH 6K Knuckle16.3
3710103355CXP558 6K Knuckle Left HandKnuckleCNC25CNC 25 LH 6K Knuckle6CNC 25 LH 6K Knuckle20.4
3810103357P558 7K Knuckle Right HandKnuckleCNC301CNC 301 RH 7K Knuckle6CNC 301 RH 7K Knuckle20.4
3910103357P558 7K Knuckle Right HandKnuckleCNC302CNC 302 RH 7K Knuckle6CNC 302 RH 7K Knuckle20.4
4010103357CDP558 7K Knuckle Right HandKnuckleCNC123CNC 123 7K RH6CNC 123 7K RH18.5
412004917Big Brake 7K Knuckle Right HandKnuckleCNC301CNC 301 RH 7K Knuckle6CNC 148 LH 7K Knuckle20.5
422004917Big Brake 7K Knuckle Right HandKnuckleCNC302CNC 302 RH 7K Knuckle6CNC 149 LH 7K Knuckle20.5
4310103358P558 7K Knuckle Left HandKnuckleCNC148CNC 148 LH 7K Knuckle6CNC 123 LH 7K20.1
4410103358P558 7K Knuckle Left HandKnuckleCNC149CNC 149 LH 7K Knuckle8CNC 34516.3
4510103358CDP558 7K Knuckle Left HandKnuckleCNC123CNC 123 LH 7K8Copeland 2D CNC 1763.4
462004918Big Brake 7K Knuckle Left HandKnuckleCNC148CNC 148 LH 7K Knuckle8Copeland 3D CNC 147 CNC 1668.2
472004918Big Brake 7K Knuckle Left HandKnuckleCNC149CNC 149 LH 7K Knuckle8Copeland K Body 3rd op CNC 3682.0
4810037203Navistar Vista Knuckle RHKNUCKLECNC345CNC 345
4910037207Navistar Vista Knuckle LHKNUCKLECNC345CNC 345
50001-0408-04W2D HonedOTHER/NON-AUTOCNC176Copeland 2D CNC 176
51001-0408-05W2D HonedOTHER/NON-AUTOCNC176Copeland 2D CNC 176
Sheet2


What I am trying to do is calculate the values highlighted in dark green, based on the other two table snippets. Example: The workcenter name in cell I20 is "CNC105 RH 6K Knuckle". I want to be able to use the data within the JPH Snippet table and the Capacity Review by Part Number Snippet table to summarize what the shifts/week requirement is for that workcenter. So, for "CNC105 RH 6K Knuckle" the answer is 16.9 shifts/week. This requires using the JPH Snippet table to understand the relationship between part number and workcenter to use as a filter of sorts to summarize the appropriate values in M3:M16. To further explain, the JPH Snippet table would need to have the part numbers mathematically filtered down so that only the part numbers associated with workcenter "CNC105 RH 6K Knuckle" are captured as what I'm calling criteria. Then, this parsed list of part number criterion would be used to filter the Capacity Review by Part number Snippet table to only add the Shifts/Week column values associated to that parsed list of part numbers. In this case, only part number 10103353 is associated to "CNC105 RH 6K Knuckle", so the value in cell J20 is simply 16.9. Where the importance of proper setup comes into play is for the workcenter in cell I44: "CNC345". This workcenter would mathematically filter the JPH Snippet table to provide the criterion of part numbers 10037203 and 10037207. These two part numbers then need to capture the values of 7.2 and 7.2 in cells M13 and M14, respectively, for a total of 14.4, to be stored in cell J44.

I did figure out a way today, but it's clunky. I ended up making a separate table that looks like this, with simple VLookups and SUMIF functions:
Albion JPH Rev 4.xlsx
ABCDE
118WorkcenterPart NoTotal Hours/Machine/WeekMarchines per Part NumberShifts per Week
133CNC 114 RH 6K Knuckle10103353123.7436109816.9
134CNC 118 LH 6K Knuckle10103355119.7981757816.3
135CNC 120 LH 6K Knuckle10103355119.7981757816.3
136CNC 121 RH 6K Knuckle10103353123.7436109816.9
137CNC 123 7K RH10103357CD135.3693733118.5
138CNC 123 LH 7K10103358CD147.1473155120.1
139CNC 14 31X Bracket RH2017707101.5467792113.8
140CNC 148 LH 7K Knuckle10103358150.0413777220.5
141CNC 148 LH 7K Knuckle2004918020.0
142CNC 149 LH 7K Knuckle10103358150.0413777220.5
143CNC 149 LH 7K Knuckle2004918020.0
217CNC 34510037203105.91350417.2
218CNC 34510037207105.91350417.2
Capacity Summary Sheet
Cell Formulas
RangeFormula
C133:C143,C217:C218C133=SUMIF(B$12:M$113,B133,M$12:M$113)
D133:D143,D217:D218D133=VLOOKUP(B133,A$12:N$113,11,FALSE)
E133:E143,E217:E218E133=SUMIF(B$12:M$113,B133,N$12:N$113)


With this, I can then created another SUMIF for the Workcenter, such as in cell I44, to summarize the required shifts/week of 14.4 in cell J44.

I hope this makes more sense. My question, now that I have it working, is there a cleaner way? Regards.
 
Upvote 0
Thanks for the smaller sample and more detailed explanation. See if this works for you. I have hidden some of the irrelevant columns to make the XL2BB mini-sheet a bit smaller.
The amber and blue colours that I have added were just so I could follow your written descriptions.

JasonC79.xlsm
AEGIJM
1Capacity Review by Part Number Snippet
2Part NumberParts producedActual JPHHours Required Per weekMachines Per Part NumberShifts/Week
310103353129075.013498029989.9488875816.87412876
410103353CX10485.147347741141.4622397119.29030541
510103355109905.17861266958.385406816.33611487
610103355CX12124.861222525149.7885225120.42570762
71010335737435.201500834298.6442006220.36210458
810103357CD4402.323739107135.3693733118.45946
9200491700020
101010335837195.176565567300.0827553220.46018786
1110103358CD4152.13774275147.1473155120.06554302
12200491800020
13100372033121.98105.91350417.221375271
14100372073121.98105.91350417.221375271
15001-0408-04W1190.94496942724.6579097713.362442241
16001-0408-05W00010
17
18JPH Snippet
19Part NoWorkcenterWorkcentersShifts/Week
2010103353CNC 105 RH 6K KnuckleCNC 105 RH 6K Knuckle16.87412876
2110103353CNC 108 RH 6K KnuckleCNC 108 RH 6K Knuckle16.87412876
2210103353CNC 121 RH 6K KnuckleCNC 121 RH 6K Knuckle16.87412876
2310103353CNC 291 RH 6K KnuckleCNC 291 RH 6K Knuckle16.87412876
2410103353CNC 114 RH 6K KnuckleCNC 114 RH 6K Knuckle16.87412876
2510103353CNC 285 RH 6K KnuckleCNC 285 RH 6K Knuckle16.87412876
2610103353CNC 104 RH 6K KnuckleCNC 104 RH 6K Knuckle16.87412876
2710103353CNC 322 RH 6K KnuckleCNC 322 RH 6K Knuckle16.87412876
2810103353CXCNC 26 RH 6K KnuckleCNC 26 RH 6K Knuckle19.29030541
2910103355CNC 118 LH 6K KnuckleCNC 118 LH 6K Knuckle16.33611487
3010103355CNC 292 LH 6K KnuckleCNC 292 LH 6K Knuckle16.33611487
3110103355CNC 120 LH 6K KnuckleCNC 120 LH 6K Knuckle16.33611487
3210103355CNC 106 LH 6K KnuckleCNC 106 LH 6K Knuckle16.33611487
3310103355CNC 303 LH 6K KnuckleCNC 303 LH 6K Knuckle16.33611487
3410103355CNC 310 LH 6K KnuckleCNC 310 LH 6K Knuckle16.33611487
3510103355CNC 158 LH 6K KnuckleCNC 158 LH 6K Knuckle16.33611487
3610103355CNC 290 LH 6K KnuckleCNC 290 LH 6K Knuckle16.33611487
3710103355CXCNC 25 LH 6K KnuckleCNC 25 LH 6K Knuckle20.42570762
3810103357CNC 301 RH 7K KnuckleCNC 301 RH 7K Knuckle20.36210458
3910103357CNC 302 RH 7K KnuckleCNC 302 RH 7K Knuckle20.36210458
4010103357CDCNC 123 7K RHCNC 123 7K RH18.45946
412004917CNC 301 RH 7K KnuckleCNC 148 LH 7K Knuckle20.46018786
422004917CNC 302 RH 7K KnuckleCNC 149 LH 7K Knuckle20.46018786
4310103358CNC 148 LH 7K KnuckleCNC 123 LH 7K20.06554302
4410103358CNC 149 LH 7K KnuckleCNC 34514.44275054
4510103358CDCNC 123 LH 7KCopeland 2D CNC 1763.362442241
462004918CNC 148 LH 7K KnuckleCopeland 3D CNC 147 CNC 166#CALC!
472004918CNC 149 LH 7K KnuckleCopeland K Body 3rd op CNC 368#CALC!
4810037203CNC 345
4910037207CNC 345
50001-0408-04WCopeland 2D CNC 176
51001-0408-05WCopeland 2D CNC 176
Sheet2
Cell Formulas
RangeFormula
J20:J47J20=SUM(FILTER(M$3:M$16,ISNUMBER(MATCH(A$3:A$16,FILTER(A$20:A$51,E$20:E$51=I20),0))))
 
Upvote 0
Solution
Your suggestion worked beautifully and it found 2 errors that I had in my clunky solution on the much larger file. Thank you kind sir for your help. Have a great evening.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
using advanced filter with 2 criteria one having a list & the other having text or no text
Replies
7
Views
822
RoryA
RoryA

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top