Check if a value is in a list of ranges and more

[Kensei]

Hello,

I have the following data:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Ranges<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD]Time[/TD]
[TD]Seconds[/TD]
[/TR]
[TR]
[TD]1:06:31 PM<strike></strike>[/TD]
[TD]1:07:00 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:06:54 PM<strike></strike>[/TD]
[TD]3.739[/TD]
[/TR]
[TR]
[TD]1:07:01 PM<strike></strike>[/TD]
[TD]1:07:30 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:07:15 PM<strike></strike>[/TD]
[TD]3.124[/TD]
[/TR]
[TR]
[TD]1:07:31 PM<strike></strike>[/TD]
[TD]1:08:00 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:07:56 PM<strike></strike>[/TD]
[TD]7.436[/TD]
[/TR]
[TR]
[TD]1:08:01 PM<strike></strike>[/TD]
[TD]1:08:30 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:08:28 PM<strike></strike>[/TD]
[TD]5.818[/TD]
[/TR]
[TR]
[TD]1:08:31 PM<strike></strike>[/TD]
[TD]1:09:00 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:08:56 PM<strike></strike>[/TD]
[TD]2.308[/TD]
[/TR]
[TR]
[TD]1:09:01 PM<strike></strike>[/TD]
[TD]1:09:30 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:09:18 PM<strike></strike>[/TD]
[TD]2.473[/TD]
[/TR]
[TR]
[TD]1:09:31 PM<strike></strike>[/TD]
[TD]1:10:00 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:09:36 PM<strike></strike>[/TD]
[TD]9.716[/TD]
[/TR]
[TR]
[TD]1:10:01 PM<strike></strike>[/TD]
[TD]1:10:30 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:09:40 PM<strike></strike>[/TD]
[TD]2.158[/TD]
[/TR]
[TR]
[TD]1:10:31 PM<strike></strike>[/TD]
[TD]1:11:00 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:10:11 PM<strike></strike>[/TD]
[TD]1.924[/TD]
[/TR]
[TR]
[TD]1:11:01 PM<strike></strike>[/TD]
[TD]1:11:30 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:10:47 PM<strike></strike>[/TD]
[TD]7.052[/TD]
[/TR]
[TR]
[TD]1:11:31 PM<strike></strike>[/TD]
[TD]1:12:00 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:11:56 PM<strike></strike>[/TD]
[TD]7.364[/TD]
[/TR]
[TR]
[TD]1:12:01 PM<strike></strike>[/TD]
[TD]1:12:30 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:12:37 PM<strike></strike>[/TD]
[TD]3.788[/TD]
[/TR]
[TR]
[TD]1:12:31 PM<strike></strike>[/TD]
[TD]1:13:00 PM<strike></strike>[/TD]
[TD][/TD]
[TD]1:13:06 PM<strike></strike>[/TD]
[TD]4.9[/TD]
[/TR]
[TR]
[TD]1:13:01 PM<strike></strike>[/TD]
[TD]1:13:01 PM<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have to check in which of the ranges (Column A and B) do the recorded times in column D go and to sum the seconds in column E for each range.
It seems to be a easy to do thing, but I can't figure it out.

Thank you in advance for all of your help!
Val

 

[Special-K99]

Does this work?

in C2
=MATCH(D2,A$2:A$15,1)
and copy down

Not sure what you mean by "sum the seconds in column E for each range"

Prob best if you provide some expected output.

 

[Kensei]

I added expected result in column F, also in column H I put how the result in column F is calculated. I did this result manually and I need a formula to calculated it automatically as the data files contain hundred of rows.
I hope it's clearer now what I need as result.


[TABLE="width: 700"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: center"] Time[/TD]
[TD="align: right"] Seconds[/TD]
[TD="align: right"] Ranges[/TD]
[TD][/TD]
[TD="align: right"] Result[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1:06:54 PM[/TD]
[TD="align: right"]3.739[/TD]
[TD="align: right"]1:06:31 PM[/TD]
[TD="align: right"]1:07:00 PM[/TD]
[TD="align: right"]3.739[/TD]
[TD][/TD]
[TD]=C2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1:07:15 PM[/TD]
[TD="align: right"]3.124[/TD]
[TD="align: right"]1:07:01 PM[/TD]
[TD="align: right"]1:07:30 PM[/TD]
[TD="align: right"]3.124[/TD]
[TD][/TD]
[TD]=C3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1:07:56 PM[/TD]
[TD="align: right"]7.436[/TD]
[TD="align: right"]1:07:31 PM[/TD]
[TD="align: right"]1:08:00 PM[/TD]
[TD="align: right"]7.436[/TD]
[TD][/TD]
[TD]=C4[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1:08:28 PM[/TD]
[TD="align: right"]5.818[/TD]
[TD="align: right"]1:08:01 PM[/TD]
[TD="align: right"]1:08:30 PM[/TD]
[TD="align: right"]5.818[/TD]
[TD][/TD]
[TD]=C5[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1:08:56 PM[/TD]
[TD="align: right"]2.308[/TD]
[TD="align: right"]1:08:31 PM[/TD]
[TD="align: right"]1:09:00 PM[/TD]
[TD="align: right"]2.308[/TD]
[TD][/TD]
[TD]=C6[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1:09:18 PM[/TD]
[TD="align: right"]2.473[/TD]
[TD="align: right"]1:09:01 PM[/TD]
[TD="align: right"]1:09:30 PM[/TD]
[TD="align: right"]12.189[/TD]
[TD][/TD]
[TD]=C7+C8[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]1:09:30 PM[/TD]
[TD="align: right"]9.716[/TD]
[TD="align: right"]1:09:31 PM[/TD]
[TD="align: right"]1:10:00 PM[/TD]
[TD="align: right"]2.158[/TD]
[TD][/TD]
[TD]=C9[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]1:09:40 PM[/TD]
[TD="align: right"]2.158[/TD]
[TD="align: right"]1:10:01 PM[/TD]
[TD="align: right"]1:10:30 PM[/TD]
[TD="align: right"]8.976[/TD]
[TD][/TD]
[TD]=C10+C11[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1:10:11 PM[/TD]
[TD="align: right"]1.924[/TD]
[TD="align: right"]1:10:31 PM[/TD]
[TD="align: right"]1:11:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]1:10:28 PM[/TD]
[TD="align: right"]7.052[/TD]
[TD="align: right"]1:11:01 PM[/TD]
[TD="align: right"]1:11:30 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"] Sum[/TD]
[TD="align: right"]45.748[/TD]
[TD][/TD]
[TD="align: right"] Sum[/TD]
[TD="align: right"]45.748[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance!