Complicated TRUE/FALSE Formula

I'm working with a monthly schedule for a team of people that work different shifts and a certain number of each shift needs to be met. So I'm trying to make a bunch of different formulas to say TRUE or FALSE based on if the shift criteria is met for that day. Each formula calculates for a different criteria so I know what shifts i need more of. Having more than the minimum number of shifts is fine.

One of the criteria is that there needs to be at least one person working from 9-5 or 9:30-5:30 or 10-6 and least one other person working one of the shifts the first person is not and that would return TRUE. If there are two people working the same shift but no one else working either of the other shifts and that would return FALSE. It is fine if there are more than 1 people working any of those three shifts as long as one of the other shifts is also being worked.

FYI: the different people working are categorized into different groups based on their strengths and there is a blank line between each of the groups.

Example of Data:

	A	B
1	Employee Name:	Aug 1
2	Alex	8-4
3	Bobby	9:30-5:30
4
5	Chris	11-7
6	Dakota	10-6
7	Ellis	12-8
8
9	List of TRUE/FALSE statements

So this example would have TRUE for the formula but there are many more people and looking at 20+ cells for each day is very time consuming. What would a formula like that be though?

I've figured out a formula that will tell me how many of each of those three shifts are being worked but it is long and messy:
=IF(COUNTIF(B$3:B$26,"9-5")>=1,"9","")&IF(COUNTIF(B$3:B$26,"9-5")>=2,"x"&COUNTIF(B$3:B$26,"9-5"),"")&IF(AND(COUNTIF(B$3:B$26,"9-5")>=1,OR(COUNTIF(B$3:B$26,"9:30-5:30")>=1,COUNTIF(B$3:B$26,"10-6")>=1)),", ","")&IF(COUNTIF(B$3:B$26,"9:30-5:30")>=1,"9:30","")&IF(COUNTIF(B$3:B$26,"9:30-5:30")>=2,"x"&COUNTIF(B$3:B$26,"9:30-5:30"),"")&IF(AND(COUNTIF(B$3:B$26,"9-5")>=1,COUNTIF(B$3:B$26,"9:30-5:30")>=1,COUNTIF(B$3:B$26,"10-6")>=1),", ","")&IF(COUNTIF(B$3:B$26,"10-6")>=1,"10","")&IF(COUNTIF(B$3:B$26,"10-6")>=2,"x"&COUNTIF(B$3:B$26,"10-6"),"")

bpiereder,

Given the conditions stated, this formula should work:

Please Login or Register  to view this content.

You're very welcome