Allocating a value based on multiple criteria

[chrisou]

HI Guys

I have attached a simplified excel file containing 2 worksheets (Alarm data and Summary).
I would like a value of 1 put in column D of the "Alarm data worksheet" for all the alarms which correspond to the date and start and finish time intervals in the "summary worksheet" . I have put in column E the answer I am looking for the formula to generate

Many thanks for your help

Chris

 

[Oaktree]

You can use COUNTIFS for this, where you're counting the number of SUMMARY tab rows that match on date, have a start time on or before the alarm time, and have an end time on or after the alarm time.

D2 =--(COUNTIFS(Summary!A:A,A2,Summary!B:B,"<="&B2,Summary!C:C,">="&B2)>0) copied down

 

[chrisou]

HI Oaktree
Thanks for your help. The formula works except for the first occurance which gives a false return. Otherwise the other returns which satisfy the criteria provide a true reply. I don't know why the first occurance gives a false reply despite matching the criteria...?
Chris

 

[Oaktree]

Are you saying cell D5 is giving a 0 instead of a 1?

Is its formula D5 =--(COUNTIFS(Summary!A:A,A5,Summary!B:B,"<="&B5,Summary!C:C,">="&B5)>0) ?

 

[chrisou]

Are you saying cell D5 is giving a 0 instead of a 1?

Is its formula D5 =--(COUNTIFS(Summary!A:A,A5,Summary!B:B,"<="&B5,Summary!C:C,">="&B5)>0) ?

Hi Oaktree
My error... I copied the formula into D3 instead of D2. Your formula works perfectly.
Many thanks
Chris

 

[Oaktree]

No worries, glad it's working for you!