# SUM IF Duration Is Between Certain Values

#### Frenzyy

##### New Member
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!

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### steve the fish

##### Well-known Member
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

##### New Member
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

##### Well-known Member
What time is 24:00?? It is 0:00.

#### Frenzyy

##### New Member
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

##### Well-known Member
Ok do you ever have times that cross midnight?

I do not.

#### steve the fish

##### Well-known Member
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

##### Well-known Member

=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

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