INDEX MATCH

[j4ymf]

Hello All

Its been a while and im not sure if you would use a count function or an index match

I would like to count the the number of Putty Pad (Single) on Sheet1 and put the answer in Block 5 that matches column B in block 5 Sheet
How would i write the lookup or INDEX MATCH

Thank you

output_20230601T204158_000001.xls
	B		D		F	G	H	I	J	K	L	M	N				R	S	T
382	24 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0082	5-1-A4L-B	1	CFS - D (Disc)	50	50		60 Min				Hilti
383	24 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0081	5-1-A4L-B	1	CFS- ACR (Mastic)	2500	10		60 Min				Hilti
384	24 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0083	5-1-A4L-B	1	CFS - D (Disc)	50	50		60 Min				Hilti
385	24 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0080	5-1-A4L-B	1	CFS- ACR (Mastic)	2500	10		60 Min				Hilti
386	24 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0082	5-1-A4L-B	1	CFS- ACR (Mastic)	2500	10		60 Min				Hilti
387	24 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0081	5-1-A4L-B	1	CFS- ACR (Mastic)	2500	10		60 Min				Hilti
388	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0007	5-1-A4L-B	1	CFS - D (Disc)	50	50		60 Min				Hilti
389	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0009	5-1-A4L-B	1	Putty Pad (Single)	100	70		60 Min				Protecta
390	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0006	5-1-A4L-B	1	Putty Pad (Single)	100	100		60 Min				Protecta
391	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0005	5-1-A4L-B	1	CFS - D (Disc)	50	50		60 Min				Hilti
392	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0004	5-1-A4L-B	1	Putty Pad (Double)	200	200		60 Min				Hilti
393	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0008	5-1-A4L-B	1	Putty Pad (Single)	100	70		60 Min				Protecta
394	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0007	5-1-A4L-B	1	Putty Pad (Double)	200	100		60 Min				Protecta
395	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0006	5-1-A4L-B	1	Putty Pad (Double)	200	70		60 Min				Protecta
396	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0005	5-1-A4L-B	1	Putty Pad (Single)	100	70		60 Min				Protecta
397	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0004	5-1-A4L-B	1	Putty Pad (Double)	200	100		60 Min				Protecta
398	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0003	5-1-A4L-B	1	Putty Pad (Double)	200	100		60 Min				Protecta
399	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0002	5-1-A4L-B	1	Putty Pad (Double)	200	100		60 Min				Protecta
400	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0003	5-1-A4L-B	1	Putty Pad (Double)	200	100		60 Min				Protecta
401	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0002	5-1-A4L-B	1	Putty Pad (Double)	200	100		60 Min				Protecta
402	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0001	5-1-A4L-B	1	Putty Pad (Double)	200	100		60 Min				Protecta
403	22 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0001	5-1-A4L-B	1	Putty Pad (Single)	100	100		60 Min				Hilti
404	30 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0175	5-20-A4L-B	20	CFS - D (Disc)	50	50		60 Min				Hilti
405	30 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0174	5-20-A4L-B	20	CFS- ACR (Mastic)	2500	10		60 Min				Hilti
406	30 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0173	5-20-A4L-B	20	CFS- ACR (Mastic)	2500	10		60 Min				Hilti
407	30 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0164	5-20-A4L-B	20	CFS - D (Disc)	50	50		60 Min				Hilti
408	30 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0163	5-20-A4L-B	20	CFS - D (Disc)	50	50		60 Min				Hilti
409	30 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0172	5-20-A4L-B	20	Putty Pad (Single)	100	100		60 Min				Protecta
410	30 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0162	5-20-A4L-B	20	CFS - D (Disc)	50	50		60 Min				Hilti
411	30 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0161	5-20-A4L-B	20	Putty Pad (Double)	200	70		60 Min				Protecta
412	30 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0160	5-20-A4L-B	20	Putty Pad (Single)	100	70		60 Min				Protecta
413	30 May 23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0171	5-20-A4L-B	20	Putty Pad (Double)	200	100		60 Min				Protecta
Sheet1

output_20230601T204158_000001.xls
	B	C	D	E	F	G	H	I	J
6
7
8
9			Mod No.	Type	Putty Pad (Single)	Putty Pad (Double)	CFS - D (Disc)	CFS- ACR (Mastic)
10	5-1-A4L-B		1	A4	6
11	5-2-B1L-B		2	B1
12	5-3-B1R-B		3	B1
13	5-4-A1R-B		4	A1
14	5-5-P1-B		5	P1
15	5-6-P2-B		6	P2
16	5-7-A1L-B		7	A1
17	5-8-B1L-B		8	B1
18	5-9-B1R-B		9	B1
19	5-10-A4R-B		10	A4
20	5-11-A4R-B		11	A4
21	5-12-B1R-B		12	B1
22	5-13-B1L-B		13	B1
23	5-14-A1L-B		14	A1
24	5-17-A1R-B		15	A1
25	5-18-B1R-B		16	B1
26	5-19-B1L-B		17	B1
27	5-20-A4L-B		18	A4
28	5-21-A4L-M		19	A4
29	5-22-B1L-M		20	B1
Block 5

 

[kweaver]

How about:

=COUNTIFS(Sheet1!$G$2:$G$33,'Block 5'!$B10,Sheet1!$I$2:$I$33,'Block 5'!F$9)

with the ranges changed accordingly/based on your data?

 

[AhoyNC]

Maybe something like this.
Change ranges to match your data. Drag formula down and across as needed.

Book4
	A	B	C	D	E	F	G	H	I	J
9				Mod No.	Type	Putty Pad (Single)	Putty Pad (Double)	CFS - D (Disc)	CFS- ACR (Mastic)
10		5-1-A4L-B		1	A4	5	9	4	4
11		5-2-B1L-B		2	B1	0	0	0	0
12		5-3-B1R-B		3	B1	0	0	0	0
13		5-4-A1R-B		4	A1	0	0	0	0
14		5-5-P1-B		5	P1	0	0	0	0
15		5-6-P2-B		6	P2	0	0	0	0
16		5-7-A1L-B		7	A1	0	0	0	0
17		5-8-B1L-B		8	B1	0	0	0	0
18		5-9-B1R-B		9	B1	0	0	0	0
19		5-10-A4R-B		10	A4	0	0	0	0
20		5-11-A4R-B		11	A4	0	0	0	0
21		5-12-B1R-B		12	B1	0	0	0	0
22		5-13-B1L-B		13	B1	0	0	0	0
23		5-14-A1L-B		14	A1	0	0	0	0
24		5-17-A1R-B		15	A1	0	0	0	0
25		5-18-B1R-B		16	B1	0	0	0	0
26		5-19-B1L-B		17	B1	0	0	0	0
27		5-20-A4L-B		18	A4	2	2	4	2
28		5-21-A4L-M		19	A4	0	0	0	0
29		5-22-B1L-M		20	B1	0	0	0	0
30
31
32
33
34		24-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0082	5-1-A4L-B	1	CFS - D (Disc)
35		24-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0081	5-1-A4L-B	1	CFS- ACR (Mastic)
36		24-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0083	5-1-A4L-B	1	CFS - D (Disc)
37		24-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0080	5-1-A4L-B	1	CFS- ACR (Mastic)
38		24-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0082	5-1-A4L-B	1	CFS- ACR (Mastic)
39		24-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0081	5-1-A4L-B	1	CFS- ACR (Mastic)
40		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0007	5-1-A4L-B	1	CFS - D (Disc)
41		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0009	5-1-A4L-B	1	Putty Pad (Single)
42		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0006	5-1-A4L-B	1	Putty Pad (Single)
43		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0005	5-1-A4L-B	1	CFS - D (Disc)
44		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0004	5-1-A4L-B	1	Putty Pad (Double)
45		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0008	5-1-A4L-B	1	Putty Pad (Single)
46		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0007	5-1-A4L-B	1	Putty Pad (Double)
47		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0006	5-1-A4L-B	1	Putty Pad (Double)
48		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0005	5-1-A4L-B	1	Putty Pad (Single)
49		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0004	5-1-A4L-B	1	Putty Pad (Double)
50		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0003	5-1-A4L-B	1	Putty Pad (Double)
51		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0002	5-1-A4L-B	1	Putty Pad (Double)
52		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0003	5-1-A4L-B	1	Putty Pad (Double)
53		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0002	5-1-A4L-B	1	Putty Pad (Double)
54		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0001	5-1-A4L-B	1	Putty Pad (Double)
55		22-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0001	5-1-A4L-B	1	Putty Pad (Single)
56		30-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0175	5-20-A4L-B	20	CFS - D (Disc)
57		30-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0174	5-20-A4L-B	20	CFS- ACR (Mastic)
58		30-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0173	5-20-A4L-B	20	CFS- ACR (Mastic)
59		30-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0164	5-20-A4L-B	20	CFS - D (Disc)
60		30-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0163	5-20-A4L-B	20	CFS - D (Disc)
61		30-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0172	5-20-A4L-B	20	Putty Pad (Single)
62		30-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0162	5-20-A4L-B	20	CFS - D (Disc)
63		30-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0161	5-20-A4L-B	20	Putty Pad (Double)
64		30-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Sam Sheldon	SS-0160	5-20-A4L-B	20	Putty Pad (Single)
65		30-May-23		Lighthouse - Sheffield Factory - Block 5 - 00		Jason Williams	JW-0171	5-20-A4L-B	20	Putty Pad (Double)
Sheet1

Cell Formulas
Range	Formula
F10:I29	F10	=COUNTIFS($J$34:$J$65,F$9,$H$34:$H$65,$B10)

 

[j4ymf]

Good morning AhoyNC & kweaver

Wow, you are both stars, thank you so much
Thank you

 

[kweaver]

Glad to have helped. Please mark it solved.