countif problem

**SRussell** · 10-22-2007, 11:52 AM

I habe a sheet set up to record free pour tests for my bar team.

Column A has the date.
Alternating columns from B (B..D..F.. etc) hold a drop down with the staff names
Alternating columns from C (C..E..G.. etc) hold a drop down with either pass or fail as the result.

What I need to do is count the number of times a particular staff name appears, but more importantly how many times they pass or fail.

I can easily count the names, but how do I count if they have pass or failed?

**daddylonglegs** · 10-22-2007, 12:06 PM

usually it's not advisable to use this sort of setup, it makes it harder to count, sum, lookup etc. but assuming your range is B2:Y10 and you don't have any staff called "pass"....

=SUMPRODUCT(--($B$2:$X$10=A1),--($C$2:$Y$10="pass"))

to count the number of passes for a member of staff shown in A1

**lazyme** · 10-22-2007, 12:06 PM

Please Login or Register  to view this content.

**SRussell** · 10-22-2007, 02:24 PM

Originally Posted by daddylonglegs

usually it's not advisable to use this sort of setup, it makes it harder to count, sum, lookup etc. but assuming your range is B2:Y10 and you don't have any staff called "pass"....

=SUMPRODUCT(--($B$2:$X$10=A1),--($C$2:$Y$10="pass"))

to count the number of passes for a member of staff shown in A1

thanks for the help, you're formula work well.

Had a little think about what you said about this method not maybe being the best way to solve this problem and this got me thinking about what I'd actually like to achieve.

Do you know a way I could count the number of successive fails (up to 3) whioch at that point would highlight the staff members name in red. The staff member's name would then remain in red until they had achieved 3 successive passes??

Any help you could give would be really helpfull because I'm afraid to admit this is way above me.

**starguy** · 10-23-2007, 03:55 AM

Originally Posted by SRussell

thanks for the help, you're formula work well.

Had a little think about what you said about this method not maybe being the best way to solve this problem and this got me thinking about what I'd actually like to achieve.

Do you know a way I could count the number of successive fails (up to 3) whioch at that point would highlight the staff members name in red. The staff member's name would then remain in red until they had achieved 3 successive passes??

Any help you could give would be really helpfull because I'm afraid to admit this is way above me.

Can you attach a sample file in zip format with the required highlighted cells?

**SRussell** · 10-23-2007, 05:02 PM

I've attached the current sample I'm working on.

Columns A & B would be hidden as they simply contain data for the drop down lists.

I've managed with the help already given to count the number of passes or failures for each member of staff.

At present I've got the results for each member of staff being shown in columns T & U.

Not sure if this is the method I want to use, but I currently have it working, very crudely I might add, with conditional formatting to change the colour of the pass/fail boxes.

If possible I would rather have something similar to what I explained above.

**starguy** · 10-24-2007, 02:44 AM

If you just want to highlight the cells with Pass and Fail, you can apply condtional formatting as
Condition 1
Cell Value is: equal to: Pass (then choose colour from format tab)

Condition 2
Cell Value is: equal to: Fail (then choose colour from format tab)

**SRussell** · 10-24-2007, 11:10 AM

Originally Posted by starguy

If you just want to highlight the cells with Pass and Fail, you can apply condtional formatting as
Condition 1
Cell Value is: equal to: Pass (then choose colour from format tab)

Condition 2
Cell Value is: equal to: Fail (then choose colour from format tab)

I have something like that set up at the moment, but it's not quite what I'm looking for.

If you look at the example sheet I posted I can test a number of staff each day and record either a pass or fail.

What I want to do is highlight a member of staff if they record 3 failures over a set time frame. This member of staff will remain highlighted until they have recorded 3 successive passes.

**Dav** · 10-24-2007, 11:43 AM

what constitutes 3 fails in a row? If a person has not been tested for 2 rows and then is tested and fails is this a consecutive fail?
can a perrson be tested more than once in a given row?

Regards

Dav

**SRussell** · 10-25-2007, 01:31 AM

Originally Posted by Dav

what constitutes 3 fails in a row? If a person has not been tested for 2 rows and then is tested and fails is this a consecutive fail?
can a perrson be tested more than once in a given row?

Regards

Dav

Hi Dav

The failures will be on consecutive testings.
If a member of staff only works 3 days per week and for example works on the 1st, 3rd & 5th of one week and then 10th, 13th & 14th of the next it doesn't matter that there is no test result for the other days as the member of staff hasn't been on duty. However, if that member of staff should fail any 3 out of the 6 dates they worked over the two week period then I would want to be highlighting them as failed.

This works exactly the same for a full-time member of staff who wroks 5 days per week, so over the same two week period works 10 days, but if they fail any 3 testign days then they should be highglighted too.

Hope this answers your question?

**Dav** · 10-25-2007, 05:48 AM

well to clarify, you can only be tested once a day? you did not answer that
If i worked one day a week, I could never fail your testing regime, is this correct?

Regards

Dav

**SRussell** · 10-25-2007, 05:33 PM

Originally Posted by Dav

well to clarify, you can only be tested once a day? you did not answer that
If i worked one day a week, I could never fail your testing regime, is this correct?

Regards

Dav

yes staff will only be tested once per day (i.e at the start of their shift)

If you only worked once per week you could still fail (if you fail three consecutive tests - over the space of 3 weeks)