CountIfs spanning two date range across Midnight

[Guzzlr]

Hello All
I'm trying to count the number of entries that the Time in column U falls within the time ranges in Q2 & R2, based upon the dates in column AA.
Because the time frame (Q2 & R2) cross midnight into the next day. I am attempting to add the the next date in AA.
For example, If 2nd shift begins on 1-3-2022 (AA3) and ends the next day on 1-4-2022 (AA4) at 4:00am, then those two dates need to be combined to form one segment of 2nd shift.
Kind of hard to explain
In AE3 I have the below formula to try and add the two dates together to form one segment

=COUNTIFS($T:$T,$AA3,$U:$U,$U3>=$Q$2)&(COUNTIFS($T:$T,$AA4,$U:$U,$U3<=$R$2))

counting 1st shift I can do as it falls on the same date, with out crossing midnight to the next day.

thank you for your help

 

[Anthony47]

I should suggest using

=SUMPRODUCT(--(($T$1:$T$1000+$U$1:$U$1000)>=(AA2+$Q$2)),--(($T$1:$T$00+$U$1:$U$1000)<=(AA2+1+$R$2)))

Bye

 

[Guzzlr]

Thanks for you help.
I used:

=SUMPRODUCT(--(($T$1:$T$1000+$U$1:$U$1000)>=(AA3+$Q$2)),--(($T$1:$T$1000+$U$1:$U$1000)<=(AA3+1+$R$2)))

Result was a #VALUE error

 

[Guzzlr]

I changed it around a bit, and seems to be working:

=SUMPRODUCT(--(($T$3:$T$1000+$U$3:$U$1000)>=($AA4+$Q$2)),--(($T$3:$T$1000+$U$3:$U$1000)<=($AA4+1+$R$2)))

Thank you
Not following how SUMPRODUCT is working for a COUNTIFS...that seems odd. but it is working.

 

[Anthony47]

The error was caused by the strings in the headers, that I didn't insert in my test workbook.
You fixed it, good.

Bye

 

[Guzzlr]

The error was caused by the strings in the headers, that I didn't insert in my test workbook.
You fixed it, good.

Bye

Just out of curiosity, what does the dashes mean?
--

 

[Anthony47]

($T$3:$T$1000+$U$3:$U$1000)>=($AA4+$Q$2) will return a list of True/False; the "--" forces those T/F to become 1/0 that SumProduct will work on.

Bye