Re: Help with sumif formula for time ranges
Hi Bezzina, it looks like you want your analysis on a line by line level, which can be done using HOUR and MINUTE along with a nested IF. I've broken it into several parts to help reading, and because there's a lot of repetition so I'm looking to reduce the total number of calculations
I've placed your sample times in cells A2:B3
I then get the key outputs for simplicity in reading and creating the main formulas
D2 =HOUR(A2)
E2 =MINUTE(A2)
F2 =HOUR(B2)
G2 =MINUTE(B2)
Headers next
I2:AG2 = 0, 1, 2 etc
Main formula in I2, copies across and down:
=IF($D2=I$1,60-$E2,IF($F2=I$1,$G2,IF(AND($D2<I$1,$F2>I$1),60,0)))