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:
[TABLE="width: 429"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Time of Incident[/TD]
[TD]Type of Incident[/TD]
[TD]Mechanism of Accident:[/TD]
[TD]Treatment of Injury[/TD]
[/TR]
[TR]
[TD]10:50am[/TD]
[TD]Accident[/TD]
[TD]5[/TD]
[TD]First aid only[/TD]
[/TR]
[TR]
[TD]11:10am[/TD]
[TD]Accident[/TD]
[TD]5[/TD]
[TD]First aid only[/TD]
[/TR]
[TR]
[TD]12:00am[/TD]
[TD]Discomfort[/TD]
[TD]3[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]9:00am[/TD]
[TD]Accident[/TD]
[TD]5[/TD]
[TD]First aid only[/TD]
[/TR]
[TR]
[TD]3:00pm[/TD]
[TD]Discomfort[/TD]
[TD]8[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]10:00am[/TD]
[TD]Discomfort[/TD]
[TD]3[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]6:15am[/TD]
[TD]Discomfort[/TD]
[TD]6[/TD]
[TD]Hospitalisation[/TD]
[/TR]
[TR]
[TD]3:10pm[/TD]
[TD]Discomfort[/TD]
[TD]3[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]3:30pm[/TD]
[TD]Near Miss[/TD]
[TD]9[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]3:46am[/TD]
[TD]Accident[/TD]
[TD]5[/TD]
[TD]First aid only[/TD]
[/TR]
[TR]
[TD]2:30am[/TD]
[TD]Discomfort[/TD]
[TD]3[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]1:00am[/TD]
[TD]Accident[/TD]
[TD]5[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]7:00am[/TD]
[TD]Near Miss[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Reference:
[TABLE="width: 249"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Mechanism of Accident[/TD]
[/TR]
[TR]
[TD]1 [/TD]
[TD]Fall, trip or slip[/TD]
[/TR]
[TR]
[TD]2 [/TD]
[TD]Sound or pressure[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Body stressing[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mental stress[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Hitting objects with part of the body[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Biological factors[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Heat, radiation or energy[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Chemical or other substances[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Being hit by moving objects[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 235"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Mechanism of accident[/TD]
[/TR]
[TR]
[TD]Fall, trip or slip[/TD]
[/TR]
[TR]
[TD]Sound or pressure[/TD]
[/TR]
[TR]
[TD]Body stressing[/TD]
[/TR]
[TR]
[TD]Mental stress[/TD]
[/TR]
[TR]
[TD]Hitting objects with part of the body[/TD]
[/TR]
[TR]
[TD]Biological factors[/TD]
[/TR]
[TR]
[TD]Heat, radiation or energy[/TD]
[/TR]
[TR]
[TD]Chemical or other substances[/TD]
[/TR]
[TR]
[TD]Being hit by moving objects[/TD]
[/TR]
</tbody>[/TABLE]

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
Data:
Time of Incident	Type of Incident	Treatment of Injury	Mechanism of accident	Mechanism of Accident
10:50am	Accident	First aid only	Fall, trip or slip	Fall, trip or slip
11:10am	Accident	First aid only	Sound or pressure	Sound or pressure
12:00am	Discomfort	None	Body stressing	Body stressing
9:00am	Accident	First aid only	Mental stress	Mental stress
3:00pm	Discomfort	None	Hitting objects with part of the body	Hitting objects with part of the body
10:00am	Discomfort	None	Biological factors	Biological factors
6:15am	Discomfort	Hospitalisation	Heat, radiation or energy	Heat, radiation or energy
3:10pm	Discomfort	None	Chemical or other substances	Chemical or other substances
3:30pm	Near Miss	None	Being hit by moving objects	Being hit by moving objects
3:46am	Accident	First aid only
2:30am	Discomfort	None
1:00am	Accident	None
7:00am	Near Miss

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Reference:[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]Mechanism of Accident:[/TD]

[TD="align: right"][/TD]

[TD="align: center"]Count of Incidences[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]9[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>

KathyFroy​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3 and down
[/TH]
[TD="align: left"]=COUNTIF($C$3:$C$15,INDEX($I$3:$I$11,MATCH(F3,$J$3:$J$11,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[kathyfroy]

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