Please help me with this octopus of a formula. Here's the problem: i need to keep the track of people who use alcohol at work and to show that information as sum of all inspections and number of inspections without problems, where one inspection can be done on one or more people. Problem is also that people can be our own workers or contractors. So lets say we have alcotested one group of 10 people which is consisted of 7 own workers and 3 of contractor. In sum that is 2 inspections (one of own workers and one of contractors). Problem is that i need formula to show if one group had one or more workers under influence of alcohol then whole inspection of the group must be shown as "positive".
Here's the example of data:
A.date B.location C.worker D.own/contractor E.positive (y/n)
by combining every data from every column i get for example "date&location&own" in column "F" for those 10 workers. It will only differ in "own/contractor"
From that i can calculate how much inspections there were with formula =IF(COUNTIF(F:F;F:F)=0;"";1/COUNTIF(F:F;F:F)) and it would show as 1 inspection of own and 1 inspection of contractor workers. So for my report it would say:
number of inspections: 2
number of inspections W/O problems: 2
Problem is how to calculate number of inspections when one or more people have been drinking (lets say only in own workers group) and when the report would have to say:
number of inspections: 2
number of inspections W/O problems: 1 (only one because contractor group didn't have any problems)
So, how to calculate group differently if only one worker from group did drink
Bookmarks