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

[Kensei]

Hello,

I have the following data:

A	B	C	D	E
Ranges<strike></strike>			Time	Seconds
1:06:31 PM<strike></strike>	1:07:00 PM<strike></strike>		1:06:54 PM<strike></strike>	3.739
1:07:01 PM<strike></strike>	1:07:30 PM<strike></strike>		1:07:15 PM<strike></strike>	3.124
1:07:31 PM<strike></strike>	1:08:00 PM<strike></strike>		1:07:56 PM<strike></strike>	7.436
1:08:01 PM<strike></strike>	1:08:30 PM<strike></strike>		1:08:28 PM<strike></strike>	5.818
1:08:31 PM<strike></strike>	1:09:00 PM<strike></strike>		1:08:56 PM<strike></strike>	2.308
1:09:01 PM<strike></strike>	1:09:30 PM<strike></strike>		1:09:18 PM<strike></strike>	2.473
1:09:31 PM<strike></strike>	1:10:00 PM<strike></strike>		1:09:36 PM<strike></strike>	9.716
1:10:01 PM<strike></strike>	1:10:30 PM<strike></strike>		1:09:40 PM<strike></strike>	2.158
1:10:31 PM<strike></strike>	1:11:00 PM<strike></strike>		1:10:11 PM<strike></strike>	1.924
1:11:01 PM<strike></strike>	1:11:30 PM<strike></strike>		1:10:47 PM<strike></strike>	7.052
1:11:31 PM<strike></strike>	1:12:00 PM<strike></strike>		1:11:56 PM<strike></strike>	7.364
1:12:01 PM<strike></strike>	1:12:30 PM<strike></strike>		1:12:37 PM<strike></strike>	3.788
1:12:31 PM<strike></strike>	1:13:00 PM<strike></strike>		1:13:06 PM<strike></strike>	4.9
1:13:01 PM<strike></strike>	1:13:01 PM<strike></strike>

<tbody>
</tbody>

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.

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

<tbody>
</tbody>

Thank you in advance!