Formula Assistance

Jimmypop

Well-known Member
Joined
Sep 12, 2013
Messages
753
Office Version
  1. 365
Platform
  1. Windows
Good day all

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

Scenario

Sheet1

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1SiteBiological HazardChemicals and Hazardous MaterialsConfined SpacesCranes / Lifting / RiggingElectrical SafetyEmergency PreparednessEnvironmentalEnvironmental Temp ExposuresErgonomicsFire / Hot WorkFleet Vehicle SafetyHand ToolsHazardous Energy (LOTO)Housekeeping (5S / 6S)IlluminationLine of FireMachine GuardingMaterial Handling / StorageMobile MachineryOccupational Exposure (Noise Dust Fumes Gases etc)Pinch PointPower / Pneumatic / Hydraulic ToolsPPEProceduralRotating EquipmentSecuritySharp ObjectSignage / NotificationsWalking / Working SurfacesWaste ManagementWildlifeWorking at HeightsPoperty Damage
2Site1
3Site2
4Site3
5Site4
6Site5
7Site6
8Site7
9Site8
10Site9
11Site10
12Site11
13Site12
14Site13
15Site14
16Site15
17Site16
18Site17
19Site18
20Site19
21Site20
22Site21
23Site22
24Site23
25Site24
26Site25
27Site26
28Site27
29Site28
30Site29
31Site30
32Site31
33Site32
34Site33
35Site34
36Site35
37Site36
38Site37
39Site38
40Site39
Sheet1



Sheet2

Book1
ABCDEF
1Submitted DateSiteReported byDescriptionAction TakenCategory
230-Sep-21Site1Person 1More than 40km/h overspeed in a 60km/h & 80km/h Manager to actionFleet Vehicle Safety
330-Sep-21Site2Person 2More than 40km/h overspeed in a 60km/h & 80km/h Manager to actionFleet Vehicle Safety
430-Sep-21Site3Person 3More than 40km/h overspeed in a 60km/h Manager to actionFleet Vehicle Safety
530-Sep-21Site4Person 4More than 40km/h overspeed in a 60km/h & 80km/h Manager to actionFleet Vehicle Safety
630-Sep-21Site5Person 5More than 40km/h overspeed in a 60km/h & 80km/h Manager to actionFleet Vehicle Safety
730-Sep-21Site6Person 6More than 40km/h overspeed in a 60km/h & 80km/h Manager to actionFleet Vehicle Safety
830-Sep-21Site7Person 7More than 40km/h overspeed in a 60km/h & 80km/h Manager to actionFleet Vehicle Safety
930-Sep-21Site8Person 8More than 40km/h overspeed in a 60km/h Manager to actionFleet Vehicle Safety
1030-Sep-21Site9Person 9More than 40km/h overspeed in a 60km/h Manager to actionFleet Vehicle Safety
1130-Sep-21Site10Person 10More than 40km/h overspeed in a 60km/h Manager to actionFleet Vehicle Safety
1230-Sep-21Site11Person 11More than 40km/h overspeed in a 60km/h & 80km/h Manager to actionFleet Vehicle Safety
1330-Sep-21Site12Person 12More than 40km/h overspeed in a 60km/h Manager to actionFleet 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
ALMN
1SiteFleet Vehicle SafetyHand ToolsHazardous Energy (LOTO)
2Site11
3Site21
4Site31
5Site41
6Site51
7Site61
8Site71
9Site81
10Site91
11Site101
12Site111
13Site121
14Site13
15Site14
Sheet1
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Check this - Column S

All Records.xlsb
HIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
1SiteBiological HazardChemicals and Hazardous MaterialsConfined SpacesCranes / Lifting / RiggingElectrical SafetyEmergency PreparednessEnvironmentalEnvironmental Temp ExposuresErgonomicsFire / Hot WorkFleet Vehicle SafetyHand ToolsHazardous Energy (LOTO)Housekeeping (5S / 6S)IlluminationLine of FireMachine GuardingMaterial Handling / StorageMobile MachineryOccupational Exposure (Noise Dust Fumes Gases etc)Pinch PointPower / Pneumatic / Hydraulic ToolsPPEProceduralRotating EquipmentSecuritySharp ObjectSignage / NotificationsWalking / Working SurfacesWaste ManagementWildlifeWorking at HeightsPoperty Damage
2Site11
3Site21
4Site31
5Site41
6Site51
7Site61
8Site71
9Site81
10Site91
11Site101
12Site111
13Site121
14Site130
15Site140
16Site150
17Site160
18Site170
19Site180
20Site190
21Site200
22Site210
23Site220
24Site230
25Site240
26Site250
27Site260
28Site270
29Site280
30Site290
31Site300
32Site310
33Site320
34Site330
35Site340
36Site350
37Site360
38Site370
39Site380
40Site390
Sheet1
Cell Formulas
RangeFormula
S2:S40S2=COUNTIFS($B$2:$B$13,$H2,$F$2:$F$13,$S$1)
 
Upvote 0
How about
Excel Formula:
=COUNTIFS(Sheet2!$B:$B,$A2,Sheet2!$F:$F,B$1)
 
Upvote 0
Solution
How about
Excel Formula:
=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....
 
Upvote 0
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...

Excel Formula:
=COUNTIFS(Sheet2!$B:$B,Sheet2!$F:$F,A$1)
 
Upvote 0
You are also missing the criteria for the 1st range. ;)
 
Upvote 0

Forum statistics

Threads
1,223,975
Messages
6,175,746
Members
452,667
Latest member
vanessavalentino83

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