combining count and lookup function

[kathyfroy]

I would like to perform a count of data, based on the number of times an item is referenced. On one sheet I have the items I wish to count, the data on a second, and the references in a third.
My data returns a number for the column i would like to count, but this needs to be 'interpreted' into a category. Any help would be appreciated

Data:

Time of Incident	Type of Incident	Mechanism of Accident:	Treatment of Injury
10:50am	Accident	5	First aid only
11:10am	Accident	5	First aid only
12:00am	Discomfort	3	None
9:00am	Accident	5	First aid only
3:00pm	Discomfort	8	None
10:00am	Discomfort	3	None
6:15am	Discomfort	6	Hospitalisation
3:10pm	Discomfort	3	None
3:30pm	Near Miss	9	None
3:46am	Accident	5	First aid only
2:30am	Discomfort	3	None
1:00am	Accident	5	None
7:00am	Near Miss	9

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Reference:

Mechanism of Accident
1	Fall, trip or slip
2	Sound or pressure
3	Body stressing
4	Mental stress
5	Hitting objects with part of the body
6	Biological factors
7	Heat, radiation or energy
8	Chemical or other substances
9	Being hit by moving objects

<colgroup><col><col></colgroup><tbody>
</tbody>

Analysis: (where I want it to say how many times each item has occurred in the data)

Mechanism of accident

Fall, trip or slip

Sound or pressure

Body stressing

Mental stress

Hitting objects with part of the body

Biological factors

Heat, radiation or energy

Chemical or other substances

Being hit by moving objects

<colgroup><col></colgroup><tbody>
</tbody>

T.I.A.

 

[Toadstool]

I've put them onto one sheet for convenience but of course you can cut & paste the sections to other tabs.

	A	B	C	D	E	F	G	H	I	J
1	Data:								Reference:
2	Time of Incident	Type of Incident	Mechanism of Accident:	Treatment of Injury		Mechanism of accident	Count of Incidences		Mechanism of Accident
3	10:50am	Accident	5	First aid only		Fall, trip or slip	0		1	Fall, trip or slip
4	11:10am	Accident	5	First aid only		Sound or pressure	0		2	Sound or pressure
5	12:00am	Discomfort	3	None		Body stressing	4		3	Body stressing
6	9:00am	Accident	5	First aid only		Mental stress	0		4	Mental stress
7	3:00pm	Discomfort	8	None		Hitting objects with part of the body	5		5	Hitting objects with part of the body
8	10:00am	Discomfort	3	None		Biological factors	1		6	Biological factors
9	6:15am	Discomfort	6	Hospitalisation		Heat, radiation or energy	0		7	Heat, radiation or energy
10	3:10pm	Discomfort	3	None		Chemical or other substances	1		8	Chemical or other substances
11	3:30pm	Near Miss	9	None		Being hit by moving objects	2		9	Being hit by moving objects
12	3:46am	Accident	5	First aid only
13	2:30am	Discomfort	3	None
14	1:00am	Accident	5	None
15	7:00am	Near Miss	9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

KathyFroy​

Worksheet Formulas Cell Formula G3 and down =COUNTIF($C$3:$C$15,INDEX($I$3:$I$11,MATCH(F3,$J$3:$J$11,0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[kathyfroy]

Thanks Toadstool that worked perfectly. Not quite confident enough with index and match to use them on my own!