Count number of screws

[JR111]

Hello,

I have an Excel sheet where I count the number of screws needed to make a product.

For every product I define the screw type, diameter, length and quantity.

I then need to count and order a final quantity. But there are several repeats of screws across the sheet, so I need to SUM those together.

I could have done this manually, but, I will be adding to the sheet with over 200 rows.

Is there a way to advise how many CS M4 8 screws are needed across the entire sheet? I can then work out the rest with a given formula.

Thanks!

737SS Screws.xlsx
	A	B	C	D	E	F
1	Part	Head type	Thread	Length	Quantity	Usage
2	MIP Screens	CS	M4	16	3	Knobs
3		CS	M4	8	12	Screens
4	Nose Wheel	DH	M4	20	2	Panel
5	Chrono	DH	M2	8	2	Microswitch
6		CS	M4	6	1	Stepper
7		DH	M3	20	2	Faceplate
8		DH	M4	6	6	Stepper
9		DH	M4	8	5	Stepper
10	Lights Test	DH	M4	12	2	Panel
11	Flaps Gauge	CS	M4	16	2	Faceplate
12		DH	M4	50	3	Backplate
13		DH	M4	20	2	Panel
14	Knee Panel	CS	M4	12	10	PLA
15		CS	M4	8	10	Brace
16		CS	M4	8	4	Rail
17		DH	M4	22	6	Rail panel
18	AFDS	DH	M3	6	4	PCB PLA
19		DH	M4	20	4	Front panel
20		DH	M4	20	2	Rear panel
21	CPT DU	DH	M4	25	2	Front panel
22	Korry 318	DH	M3	12	15	Assembly
23	Autobrake Panel	DH	M3	12	1	Cage mount
24		DH	M4	10	6	Antiskid and mount
25		DH	M4	25	4	Front panel
26	Rotary Slew Switch	SHB	M4	12	2
27		SHB	M4	25	4	Orrifices
28		SHB	M4	55	4
29		CS	M4	8	4	Slew cap cover
30		DH	M3	8	2
31		DH	M2	16	8	Microswitch
32		DH	M4	30	1
33		DH	M3	10	2
34		DH	M4	16	6	Stop in
35		DH	M3	12	2
36	Anti skid	DH	M4	10	6
37	ISFD	CS	M4	16	1	Button
38		CS	M3	10	2	Rear panel
39	RMI	DH	M4	8	4	Front panel
40		DH	M3	16	2	Knobs
Sheet1

 

[awoohaw]

Here is a solution, but you'll need to copy down the header values of column A (Can be done easily with FIND BLANKS and typing = and [arrow up] key then CNTR-ENTR).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Part	Head type	Thread	Length	Quantity	Usage			Head type	Thread	Length
2	MIP Screens	CS	M4	16	3	Knobs		MIP Screens	CS	M4	16	6
3	MIP Screens	CS	M4	8	12	Screens		MIP Screens	CS	M4	8	30
4	Nose Wheel	DH	M4	20	2	Panel		Nose Wheel	DH	M4	20	10
5	Chrono	DH	M2	8	2	Microswitch		Chrono	DH	M2	8	2
6	Chrono	CS	M4	6	1	Stepper		Chrono	CS	M4	6	1
7	Chrono	DH	M3	20	2	Faceplate		Chrono	DH	M3	20	2
8	Chrono	DH	M4	6	6	Stepper		Chrono	DH	M4	6	6
9	Chrono	DH	M4	8	5	Stepper		Chrono	DH	M4	8	9
10	Lights Test	DH	M4	12	2	Panel		Lights Test	DH	M4	12	2
11	Flaps Gauge	CS	M4	16	2	Faceplate		Flaps Gauge	DH	M4	50	3
12	Flaps Gauge	DH	M4	50	3	Backplate		Knee Panel	CS	M4	12	10
13	Flaps Gauge	DH	M4	20	2	Panel		Knee Panel	DH	M4	22	6
14	Knee Panel	CS	M4	12	10	PLA		AFDS	DH	M3	6	4
15	Knee Panel	CS	M4	8	10	Brace		CPT DU	DH	M4	25	6
16	Knee Panel	CS	M4	8	4	Rail		Korry 318	DH	M3	12	18
17	Knee Panel	DH	M4	22	6	Rail panel		Autobrake Panel	DH	M4	10	12
18	AFDS	DH	M3	6	4	PCB PLA		Rotary Slew Switch	SHB	M4	12	2
19	AFDS	DH	M4	20	4	Front panel		Rotary Slew Switch	SHB	M4	25	4
20	AFDS	DH	M4	20	2	Rear panel		Rotary Slew Switch	SHB	M4	55	4
21	CPT DU	DH	M4	25	2	Front panel		Rotary Slew Switch	DH	M3	8	2
22	Korry 318	DH	M3	12	15	Assembly		Rotary Slew Switch	DH	M2	16	8
23	Autobrake Panel	DH	M3	12	1	Cage mount		Rotary Slew Switch	DH	M4	30	1
24	Autobrake Panel	DH	M4	10	6	Antiskid and mount		Rotary Slew Switch	DH	M3	10	2
25	Autobrake Panel	DH	M4	25	4	Front panel		Rotary Slew Switch	DH	M4	16	6
26	Rotary Slew Switch	SHB	M4	12	2			ISFD	CS	M3	10	2
27	Rotary Slew Switch	SHB	M4	25	4	Orrifices		RMI	DH	M3	16	2
28	Rotary Slew Switch	SHB	M4	55	4
29	Rotary Slew Switch	CS	M4	8	4	Slew cap cover
30	Rotary Slew Switch	DH	M3	8	2
31	Rotary Slew Switch	DH	M2	16	8	Microswitch
32	Rotary Slew Switch	DH	M4	30	1
33	Rotary Slew Switch	DH	M3	10	2
34	Rotary Slew Switch	DH	M4	16	6	Stop in
35	Rotary Slew Switch	DH	M3	12	2
36	Anti skid	DH	M4	10	6
37	ISFD	CS	M4	16	1	Button
38	ISFD	CS	M3	10	2	Rear panel
39	RMI	DH	M4	8	4	Front panel
40	RMI	DH	M3	16	2	Knobs
41
42
Sheet2

Cell Formulas
Range	Formula
I1:K27	I1	=UNIQUE($B$1:$D$40,FALSE,FALSE)
A3,A40,A38,A27:A35,A24:A25,A19:A20,A15:A17,A12:A13,A6:A9	A3	=A2
H2	H2	=XLOOKUP(I2&J2&K2,$B$2:$B$40 & $C$2:$C$40 & $D$2:$D$40, $A$2:$A$40, "Not Found",0,1)
H3:H27	H3	=XLOOKUP(I3&J3&K3,$B$2:$B$40&$C$2:$C$40&$D$2:$D$40,$A$2:$A$40,"Not Found",0,1)
L2:L27	L2	=SUMIFS($E$2:$E$40,$B$2:$B$40,I2,$C$2:$C$40,J2,$D$2:$D$40,K2)
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Part	Head type	Thread	Length	Quantity	Usage
2	MIP Screens	CS	M4	16	3	Knobs		CS	M4	16	6
3		CS	M4	8	12	Screens		CS	M4	8	30
4	Nose Wheel	DH	M4	20	2	Panel		DH	M4	20	10
5	Chrono	DH	M2	8	2	Microswitch		DH	M2	8	2
6		CS	M4	6	1	Stepper		CS	M4	6	1
7		DH	M3	20	2	Faceplate		DH	M3	20	2
8		DH	M4	6	6	Stepper		DH	M4	6	6
9		DH	M4	8	5	Stepper		DH	M4	8	9
10	Lights Test	DH	M4	12	2	Panel		DH	M4	12	2
11	Flaps Gauge	CS	M4	16	2	Faceplate		DH	M4	50	3
12		DH	M4	50	3	Backplate		CS	M4	12	10
13		DH	M4	20	2	Panel		DH	M4	22	6
14	Knee Panel	CS	M4	12	10	PLA		DH	M3	6	4
15		CS	M4	8	10	Brace		DH	M4	25	6
16		CS	M4	8	4	Rail		DH	M3	12	18
17		DH	M4	22	6	Rail panel		DH	M4	10	12
18	AFDS	DH	M3	6	4	PCB PLA		SHB	M4	12	2
19		DH	M4	20	4	Front panel		SHB	M4	25	4
20		DH	M4	20	2	Rear panel		SHB	M4	55	4
21	CPT DU	DH	M4	25	2	Front panel		DH	M3	8	2
22	Korry 318	DH	M3	12	15	Assembly		DH	M2	16	8
23	Autobrake Panel	DH	M3	12	1	Cage mount		DH	M4	30	1
24		DH	M4	10	6	Antiskid and mount		DH	M3	10	2
25		DH	M4	25	4	Front panel		DH	M4	16	6
26	Rotary Slew Switch	SHB	M4	12	2			CS	M3	10	2
27		SHB	M4	25	4	Orrifices		DH	M3	16	2
28		SHB	M4	55	4
29		CS	M4	8	4	Slew cap cover
30		DH	M3	8	2
31		DH	M2	16	8	Microswitch
32		DH	M4	30	1
33		DH	M3	10	2
34		DH	M4	16	6	Stop in
35		DH	M3	12	2
36	Anti skid	DH	M4	10	6
37	ISFD	CS	M4	16	1	Button
38		CS	M3	10	2	Rear panel
39	RMI	DH	M4	8	4	Front panel
40		DH	M3	16	2	Knobs
41
Sheet6

Cell Formulas
Range	Formula
H2:J27	H2	=UNIQUE(FILTER(B2:D100,B2:B100<>""))
K2:K27	K2	=SUMIFS(E:E,B:B,INDEX(H2#,,1),C:C,INDEX(H2#,,2),D:D,INDEX(H2#,,3))
Dynamic array formulas.