SUM IF Duration Is Between Certain Values

[Frenzyy]

Hi All! Could you please kindly advise as to how to sum duration values that fall between certain ranges? My custom format is set to [h]:mm and cannot be changed.
Let me illustrate what I'm trying to do with an example.

Columns A & B usually contain 9:00 & 17:30 respectively, however, at times they may contain values that differ. E.g. 9:00 & 20:20 or 7:50 & 22:35. Now, what I'm trying to do is sum up the hours that come before "9:00" in column A and after "22:00" in column B. Meaning that the result for the latter example would be 1:10 + 0:35 = 2:45. After that, I need to sum up all the rows containing the mentioned sums but I assume that this will be a simple SUM calculation though feel free to correct me if I'm wrong.

Please let me know if you have questions.

Many thanks!

 

[steve the fish]

You could try this:

=IF(COUNT(A1:B1)=2,MAX(TIME(22,0,0),MOD(B1,1))-MIN(TIME(9,0,0),MOD(A1,1))-TIME(22,0,0)+TIME(9,0,0),0)

 

[Frenzyy]

Hi, thank you for that. However, this doesn't allow for time "24:00" to be entered as it just treats it as "0".

Please see my below solution for the "after 22:00" part and I'd appreciate if you could assist me in figuring out how to properly adjust this for the "before 9:00" as I'm not able to get it to work for some reason.

=SUMPRODUCT((range-TIME(22,0,0))*(range>TIME(22,0,0)))

Thanks!

 

[steve the fish]

What time is 24:00?? It is 0:00.

 

[Frenzyy]

That's correct but please note that the formula I posted does count it as "+2" if "24" is the input. Thanks!

 

[steve the fish]

Ok do you ever have times that cross midnight?

 

[Frenzyy]

I do not.

 

[steve the fish]

In that case you could remove the mod part:

=IF(COUNT(A1:B1)=2,MAX(TIME(22,0,0),B1)-MIN(TIME(9,0,0),A1)-TIME(22,0,0)+TIME(9,0,0),0)

 

[steve the fish]

And here is your SUMPRODUCT:

=SUMPRODUCT(((TIME(9,0,0)-A1:A10)*(A1:A10<TIME(9,0,0))*(A1:A10<>"")*(B1:B10<>""))+((B1:B10-TIME(22,0,0))*(B1:B10>TIME(22,0,0))*(A1:A10<>"")*(B1:B10<>"")))

 

[Frenzyy]

That's great, thank you very much.
Would you mind explaining a little bit as to how this part works "*(A1:A10<>"")*(B1:B10<>""))"?