SumProduct / Sumif, within 2 times

[Henry c]

Hi,

From some fleet data i am trying to total any journey which took place between two times.

The time format is hh:mm:ss. I want to total the miles travelled in each trip between 5pm and 5am for certain vehicles

Vehicle Registration is in colum A, the times of the journey are column B and the miles traveled are column D.

=SUMPRODUCT(--(A2:A100=LG52RYT),--(B2:B1000=NOT Sure???, D2:D1000)

Would I use the above formular? And what would i put in the section i have marked "not sure"?

Kind Regards,

Henry

[zbor]

Maybe something like:

=SUMPRODUCT(--(A2:A100=E1),--(B2:B1000>=E2),--(B2:B1000<=E3), D2:D1000)

where in E1 you put vhicle registration and in E2 and E3 start and end time you want to search

[Henry c]

Unfortunatly That wont work

Does anyone have any other ideas, why is excell so frustrating lol

[zbor]

Can you upload example?

[Henry c]

Yer, sure, please see attached, I have edited it due to data protection, but kept the format the same,

Kind Regards,

Henry

[DonkeyOte]

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

[Henry c]

Please see sample above,

I understand what your saying, the time has no date to link in to a certain day.

Would including the date, which in in a seperate column help?

[DonkeyOte]

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:

=SUMPRODUCT(--(LEFT($A$2:$A$100,LEN($E$1))=$E$1),--(($B$2:$B$100+((--$B$2:$B$100<=$E$3)*($E$3<$E$2)))<=($E$3+($E$3<$E$2))),--(($B$2:$B$100+((--$B$2:$B$100<=$E$3)*($E$3<$E$2)))>=$E$2),$D$2:$D$100)

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.

[Henry c]

Many Thanks for your help, i will have a go,

Kind Regards,

Henry

[daddylonglegs]

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

[DonkeyOte]

...another keeper... thanks dll