Formula Assistance

[Jimmypop]

Good day all

Stuck somewhat I did this before but unfortunately I accidentally deleted my file containing formulas I had...

Scenario

Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Site	Biological Hazard	Chemicals and Hazardous Materials	Confined Spaces	Cranes / Lifting / Rigging	Electrical Safety	Emergency Preparedness	Environmental	Environmental Temp Exposures	Ergonomics	Fire / Hot Work	Fleet Vehicle Safety	Hand Tools	Hazardous Energy (LOTO)	Housekeeping (5S / 6S)	Illumination	Line of Fire	Machine Guarding	Material Handling / Storage	Mobile Machinery	Occupational Exposure (Noise Dust Fumes Gases etc)	Pinch Point	Power / Pneumatic / Hydraulic Tools	PPE	Procedural	Rotating Equipment	Security	Sharp Object	Signage / Notifications	Walking / Working Surfaces	Waste Management	Wildlife	Working at Heights	Poperty Damage
2	Site1
3	Site2
4	Site3
5	Site4
6	Site5
7	Site6
8	Site7
9	Site8
10	Site9
11	Site10
12	Site11
13	Site12
14	Site13
15	Site14
16	Site15
17	Site16
18	Site17
19	Site18
20	Site19
21	Site20
22	Site21
23	Site22
24	Site23
25	Site24
26	Site25
27	Site26
28	Site27
29	Site28
30	Site29
31	Site30
32	Site31
33	Site32
34	Site33
35	Site34
36	Site35
37	Site36
38	Site37
39	Site38
40	Site39
Sheet1

Sheet2

Book1
	A	B	C	D	E	F
1	Submitted Date	Site	Reported by	Description	Action Taken	Category
2	30-Sep-21	Site1	Person 1	More than 40km/h overspeed in a 60km/h & 80km/h	Manager to action	Fleet Vehicle Safety
3	30-Sep-21	Site2	Person 2	More than 40km/h overspeed in a 60km/h & 80km/h	Manager to action	Fleet Vehicle Safety
4	30-Sep-21	Site3	Person 3	More than 40km/h overspeed in a 60km/h	Manager to action	Fleet Vehicle Safety
5	30-Sep-21	Site4	Person 4	More than 40km/h overspeed in a 60km/h & 80km/h	Manager to action	Fleet Vehicle Safety
6	30-Sep-21	Site5	Person 5	More than 40km/h overspeed in a 60km/h & 80km/h	Manager to action	Fleet Vehicle Safety
7	30-Sep-21	Site6	Person 6	More than 40km/h overspeed in a 60km/h & 80km/h	Manager to action	Fleet Vehicle Safety
8	30-Sep-21	Site7	Person 7	More than 40km/h overspeed in a 60km/h & 80km/h	Manager to action	Fleet Vehicle Safety
9	30-Sep-21	Site8	Person 8	More than 40km/h overspeed in a 60km/h	Manager to action	Fleet Vehicle Safety
10	30-Sep-21	Site9	Person 9	More than 40km/h overspeed in a 60km/h	Manager to action	Fleet Vehicle Safety
11	30-Sep-21	Site10	Person 10	More than 40km/h overspeed in a 60km/h	Manager to action	Fleet Vehicle Safety
12	30-Sep-21	Site11	Person 11	More than 40km/h overspeed in a 60km/h & 80km/h	Manager to action	Fleet Vehicle Safety
13	30-Sep-21	Site12	Person 12	More than 40km/h overspeed in a 60km/h	Manager to action	Fleet Vehicle Safety
Sheet2

I need to sum by site on Sheet 1 from Sheet 2 by Category on Sheet 2. On Sheet 1 Category's are each in a separate column and on sheet 2 they are all in one column...

So Site 1-12 on sheet 1 should have 1 each in column Fleet Vehicle Safety obtained from Sheet 2...

Book1
	A											L	M	N
1	Site											Fleet Vehicle Safety	Hand Tools	Hazardous Energy (LOTO)
2	Site1											1
3	Site2											1
4	Site3											1
5	Site4											1
6	Site5											1
7	Site6											1
8	Site7											1
9	Site8											1
10	Site9											1
11	Site10											1
12	Site11											1
13	Site12											1
14	Site13
15	Site14
Sheet1

 

[SanjayGMusafir]

Check this - Column S

All Records.xlsb
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1	Site	Biological Hazard	Chemicals and Hazardous Materials	Confined Spaces	Cranes / Lifting / Rigging	Electrical Safety	Emergency Preparedness	Environmental	Environmental Temp Exposures	Ergonomics	Fire / Hot Work	Fleet Vehicle Safety	Hand Tools	Hazardous Energy (LOTO)	Housekeeping (5S / 6S)	Illumination	Line of Fire	Machine Guarding	Material Handling / Storage	Mobile Machinery	Occupational Exposure (Noise Dust Fumes Gases etc)	Pinch Point	Power / Pneumatic / Hydraulic Tools	PPE	Procedural	Rotating Equipment	Security	Sharp Object	Signage / Notifications	Walking / Working Surfaces	Waste Management	Wildlife	Working at Heights	Poperty Damage
2	Site1											1
3	Site2											1
4	Site3											1
5	Site4											1
6	Site5											1
7	Site6											1
8	Site7											1
9	Site8											1
10	Site9											1
11	Site10											1
12	Site11											1
13	Site12											1
14	Site13											0
15	Site14											0
16	Site15											0
17	Site16											0
18	Site17											0
19	Site18											0
20	Site19											0
21	Site20											0
22	Site21											0
23	Site22											0
24	Site23											0
25	Site24											0
26	Site25											0
27	Site26											0
28	Site27											0
29	Site28											0
30	Site29											0
31	Site30											0
32	Site31											0
33	Site32											0
34	Site33											0
35	Site34											0
36	Site35											0
37	Site36											0
38	Site37											0
39	Site38											0
40	Site39											0
Sheet1

Cell Formulas
Range	Formula
S2:S40	S2	=COUNTIFS($B$2:$B$13,$H2,$F$2:$F$13,$S$1)

 

[Fluff]

How about

=COUNTIFS(Sheet2!$B:$B,$A2,Sheet2!$F:$F,B$1)

 

[Jimmypop]

How about

=COUNTIFS(Sheet2!$B:$B,$A2,Sheet2!$F:$F,B$1)

Thanks Fluff... Working but up to a point... some items not being counted but I discovered that my spelling is not all the same everywhere will fix this up then should work....

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Jimmypop]

UPDATE:

All spelling now the same and working as should... I did try the following formula I could remember BUT I saw now that I did not reference my columns correctly...that is why it did not work...

=COUNTIFS(Sheet2!$B:$B,Sheet2!$F:$F,A$1)

 

[Fluff]

You are also missing the criteria for the 1st range.