combining count and lookup function

K

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:
[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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
I've put them onto one sheet for convenience but of course you can cut & paste the sections to other tabs.

ABCDEFGHIJ
Data:
Time of IncidentType of IncidentTreatment of InjuryMechanism of accidentMechanism of Accident
10:50amAccidentFirst aid onlyFall, trip or slipFall, trip or slip
11:10amAccidentFirst aid onlySound or pressureSound or pressure
12:00amDiscomfortNoneBody stressingBody stressing
9:00amAccidentFirst aid onlyMental stressMental stress
3:00pmDiscomfortNoneHitting objects with part of the bodyHitting objects with part of the body
10:00amDiscomfortNoneBiological factorsBiological factors
6:15amDiscomfortHospitalisationHeat, radiation or energyHeat, radiation or energy
3:10pmDiscomfortNoneChemical or other substancesChemical or other substances
3:30pmNear MissNoneBeing hit by moving objectsBeing hit by moving objects
3:46amAccidentFirst aid only
2:30amDiscomfortNone
1:00amAccidentNone
7:00amNear 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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
count if based on specific criteria
Replies
2
Views
724
nikhil0311
N
P
  • Question Question
Transfer data from Sheet2 to Sheet1
Replies
0
Views
526
Panoos64
P
T
  • Question Question
İmport Text File With Excel Vba
Replies
1
Views
2K
tevfikuzunn
T
A
  • Question Question
Populating summary table
Replies
1
Views
418
Smitty
Smitty
P
  • Question Question
Find the number of times a number >0 appears with a criteria of a certain word
Replies
3
Views
425
Jonmo1
Jonmo1

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

Share this page

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
Back
Top