Formula Suggestions - Multiple Criteria

[Jonek2020]

Hi Experts

Please help

I am an office 365 user and I need suggestions on what formula to use for this scenario.
I have quite large data sets so I need the most efficient formulas that will not crash my workbook (+-30000 rows 69 columns).

Scenario
I need to be able to determine if a person worked on a specific day. Each person can work at multiple locations on the same day and must check in and out at each location throughout the day.
The assumption is that if all locations for that person on the day have a blank time in and blank time out that the person did not work. In that case I want the formula to return DID NOT RUN, if either condition is not blank then the formula needs to return RUN.

I have tried a sumproduct formula which evaluates all rows that are blank as TRUE which I can possibly use as part of a formula.
But that is as far as I have got as I don't have much experience with doing nested formulas

 

[fadee2]

The assumption is that if all locations for that person on the day have a blank time in and blank time out that the person did not work. In that case I want the formula to return DID NOT RUN, if either condition is not blank then the formula needs to return RUN.

What about if a person has only checkin or only checkout???
refer to person B in store 13....

 

[Jonek2020]

Hi

What about if a person has only checkin or only checkout???
refer to person B in store 13....

If a person only has 1 check in or out the status is run. Only blank times for in and out are counted as did not run.

 

[fadee2]

how about....

=IF(AND(D1="",E1=""),"Did not Run","Run")

Book1
	A	B	C	D	E	F
1	Person A	Store 1	19/10/20	8:00	12:00	Run
2	Person A	Store 2	19/10/20	12:01	14:00	Run
3	Person A	Store 3	19/10/20	14:01		Run
4	Person A	Store 4	19/10/20		17:00	Run
5	Person A	Store 5	19/10/20			Did not Run
6	Person B	Store 9	20/10/20	8:00	17:00	Run
Sheet1

Cell Formulas
Range	Formula
F1:F6	F1	=IF(AND(D1="",E1=""),"Did not Run","Run")

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F
1
2	Person A	Store 1	19/10/2020	08:00:00	12:00:00	Run
3	Person A	Store 2	19/10/2020	12:01:00	14:00:00	Run
4	Person A	Store 3	19/10/2020			Run
5	Person A	Store 4	19/10/2020		08:00:00	Run
6	Person B	Store 5	19/10/2020			Did not run
7	Person B	Store 9	20/10/2020	08:00:00	17:00:00	Run
8	Person C	Store 1	20/10/2020			Did not run
9	Person D	Store 3	19/10/2020	08:00		Run
10
Main

Cell Formulas
Range	Formula
F2:F9	F2	=IF(COUNTIFS(A:A,A2,C:C,C2)=COUNTIFS(A:A,A2,C:C,C2,D:D,"",E:E,""),"Did not run","Run")

 

[Jonek2020]

The countifs formula works perfectly for what I need as a start. I am now inspired to expand the selection for more criteria to identify the more complex elements that need to flag as Check.
That is going to be tricky but I am going to give it a go ...
Thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[fadee2]

The countifs formula works perfectly for what I need as a start. I am now inspired to expand the selection for more criteria to identify the more complex elements that need to flag as Check.
That is going to be tricky but I am going to give it a go ...
Thank you so much!