SumProduct / Sumif, within 2 times

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:

Please Login or Register  to view this content.

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...

Not really - unless you're looking to sum between specific DateTimes

The version I posted previously will sum miles where time span crosses midnight...

If you want a version whereby you can have pattern that may/may not cross midnight then I think you would need to adapt to perhaps something along the lines of:

Please Login or Register  to view this content.

in the above version I adjusted such that you could specify a Reg of varying length in E1 - eg L to sum all plates beginning with L or you can make as specific as you like (ie adding full Reg)

Note also - in your example the values in B2 onwards are actually text strings...on that basis the values are coerced in the above where necessary.

You can use this version to accommodate a time range which either spans midnight or not

=SUMPRODUCT(--(A2:A100=E1),--((B2:B100+0>=E2)+(B2:B100+0<=E3)+(E2>E3)=2), D2:D100)

© daddylonglegs 2010

...another keeper... thanks dll