combining count and lookup function

kathyfroy

New Member
Joined
Jan 16, 2018
Messages
3
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 IncidentType of IncidentMechanism of Accident:Treatment of Injury
10:50amAccident5First aid only
11:10amAccident5First aid only
12:00amDiscomfort3None
9:00amAccident5First aid only
3:00pmDiscomfort8None
10:00amDiscomfort3None
6:15amDiscomfort6Hospitalisation
3:10pmDiscomfort3None
3:30pmNear Miss9None
3:46amAccident5First aid only
2:30amDiscomfort3None
1:00amAccident5None
7:00amNear Miss9

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

Reference:
Mechanism of Accident
1 Fall, trip or slip
2 Sound or pressure
3Body stressing
4Mental stress
5Hitting objects with part of the body
6Biological factors
7Heat, radiation or energy
8Chemical or other substances
9Being 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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,362
Office Version
  1. 2016
Platform
  1. Windows
I've put them onto one sheet for convenience but of course you can cut & paste the sections to other tabs.

ABCDEFGHIJ
1Data:Reference:
2Time of IncidentType of IncidentMechanism of Accident:Treatment of InjuryMechanism of accidentCount of IncidencesMechanism of Accident
310:50amAccident5First aid onlyFall, trip or slip01Fall, trip or slip
411:10amAccident5First aid onlySound or pressure02Sound or pressure
512:00amDiscomfort3NoneBody stressing43Body stressing
69:00amAccident5First aid onlyMental stress04Mental stress
73:00pmDiscomfort8NoneHitting objects with part of the body55Hitting objects with part of the body
810:00amDiscomfort3NoneBiological factors16Biological factors
96:15amDiscomfort6HospitalisationHeat, radiation or energy07Heat, radiation or energy
103:10pmDiscomfort3NoneChemical or other substances18Chemical or other substances
113:30pmNear Miss9NoneBeing hit by moving objects29Being hit by moving objects
123:46amAccident5First aid only
132:30amDiscomfort3None
141:00amAccident5None
157:00amNear Miss9

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

Worksheet Formulas
CellFormula
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

New Member
Joined
Jan 16, 2018
Messages
3
Thanks Toadstool that worked perfectly. Not quite confident enough with index and match to use them on my own!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,983
Messages
5,621,966
Members
415,869
Latest member
LWSkinner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top