EDIT: below composed before above attachment loaded...


Henry c, zbor's solution is I think along the right lines (mis-sized ranges though) but In this case the issue is the fact that your time span crosses midnight.

Given this fact and the fact that zbor's approach utilises an AND based test it will return 0 given the time value in B can never be both greater than start (17:00) and less than end (05:00) simultaneously...

So for this scenario you can adapt to an OR test:

=SUMPRODUCT(--(A2:A1000=E1),(B2:B1000>=E2)+(B2:B1000<=E3),D2:D1000)
so in this case the values in D will be summed should either the Time be greater than 5pm or less than 5am

If that doesn't help either please post a sample as suggested already...