SUM IF Duration Is Between Certain Values

Frenzyy

New Member
Joined
May 26, 2019
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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)
 
Upvote 0
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!
 
Upvote 0
That's correct but please note that the formula I posted does count it as "+2" if "24" is the input. Thanks!
 
Upvote 0
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)
 
Upvote 0
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<>"")))
 
Upvote 0
That's great, thank you very much.
Would you mind explaining a little bit as to how this part works "*(A1:A10<>"")*(B1:B10<>""))"?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top