Formula Help

[.Justin]

I have a table that counts how many people have presented on a certain date in hour intervals. I use the following formula to gather this data:

=COUNTIFS('Patient Summary'!B6:B453,"[B]21/7/2018[/B]",'Patient Summary'!C6:C453,">=02:00",'Patient Summary'!C6:C453,"<03:00")

When writing the formula I have to enter the date manually into each cell I want it to look for the date within raw data. Is there any way I can set the formula up without having to change the date in each cell but have it look up the value I want to capture from a certain cell instead?

Thanks

 

[Aladin Akyurek]

Looks like you are asking...

=COUNTIFS('Patient Summary'!$B$6:$B4$53,X6,'Patient Summary'!$C$6:$C$453,">=02:00",'Patient Summary'!$C$6:$C$453,"<03:00")

where X2 houses a date value like 21/7/2018.