Count group direfently if one parameter is different

[gesaugen]

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

[zbor]

Try instead of number 1 (or 2) write:

Please Login or Register  to view this content.

Like this:

Please Login or Register  to view this content.

[gesaugen]

it only increases the number of total inspections by x2. that's now that I'm after... the number of inspections is calculated ok, so number 1 in formula is ok

i need to get number of inspections with positive tested workers. That parameter in column "E" (y/n) hasn't been calculated at all because i don't know how to calculate it in to get number of only positive inspections

[gesaugen]

double post

[newdoverman]

I made up some dummy data and perhaps this will do what you want.

I used a Pivot Table for a summary of data.

[gesaugen]

I made up some dummy data and perhaps this will do what you want.

I used a Pivot Table for a summary of data.

Thanks for trying but it is not exactly what i want because i need result as final number (managers want pure "yes/no" data and not something that they need to think of what are they looking at)

So here's my table with the problem - I have problems with uploading via forum console so I've uploaded via file share. here's the link:
http://s000.tinyupload.com/index.php...06778502170000

the result I need is colored yellow in table to the right. I've manually entered in yellow fields the numbers which i should get via this formula I'm asking for here on forum. everything else is calculated

So in the data sheet to the left I've colored with light green all inspections over contractor workers and white ones are own, just to show more easily what we are looking at.
It can be seen that in January there were 3 inspections of contractor and 3 of own worker and it happened in 3 different days. Own workers only had one negative inspection, that at 5.1.2015 while contractor had two, those at 5.1 and 6.1.

as shown in the table to the right, i need formula to show only number of inspections without anyone been positively tested

[zbor]

Please use Forum to upload attachments.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I've add extra column.
According to it I've got different output than your example.

Why you got 1 in 2nd month?
I guess it's a 0.

Here are mine outputs:

In January we have 2C (5.1. and 6.1.) and 1O (5.1.)
In February 0
In May 1C (8.5.)

[gesaugen]

Please use Forum to upload attachments.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I tried but it won't upload my files, it says eror. I have proxy in my company so maybe something is messing upload up.

I've add extra column.
According to it I've got different output than your example.

Why you got 1 in 2nd month?
I guess it's a 0.

Here are mine outputs:

In January we have 2C (5.1. and 6.1.) and 1O (5.1.)
In February 0
In May 1C (8.5.)

hvala!

I got 1 in 2nd month because ther was only one inspection in 2nd month and it didn't have anyone positive. so its 1 inspection and 1 inspection without "y"

your solution works only halfway: it stops when it reaches row no.100. from there onwards calculation stops and gives me error formula warning

Also it shows number of inspections with Y but i need it other way: number of inspections without "Y"

[zbor]

Yes.
In column I and rows 4 and 6 you need to extend range:

For example:

Please Login or Register  to view this content.

You can select whole column but it will be slow so I suggest to put some limited range.

[zbor]

Got it.
Fixed now.

Range extended to 10.000

[gesaugen]

zbor, than you very very much! It works perfectly!
this is way out of my excel knowledge