Date Range data retrieval

[Shan9]

Hello all,

Wondering if anyone can help me with my query; I've a spreadsheet Sheet 1 contains

[TABLE="width: 260"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 292"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]From[/TD]
[TD]To[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[TD]07/01/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Time[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]07:00:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
and Sheet 2 contains

[TABLE="width: 991"]
<colgroup><col><col span="5"><col><col span="3"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]01/01/2018[/TD]
[TD]02/01/2018[/TD]
[TD]03/01/2018[/TD]
[TD]04/01/2018[/TD]
[TD]05/01/2018[/TD]
[TD]06/01/2018[/TD]
[TD]07/01/2018[/TD]
[TD]08/01/2018[/TD]
[TD]09/01/2018[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]01:00:00[/TD]
[TD]01:00:00[/TD]
[TD]01:00:00[/TD]
[TD]01:00:00[/TD]
[TD]01:00:00[/TD]
[TD]01:00:00[/TD]
[TD]01:00:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
my question is in Sheet 1 based up on the date range either 01/01/2018 -07/01/2018 or any range
how can I retrieve total time from Sheet 2 to Sheet 1 for the corresponding Name 1 or 2 etc.. in the example above over a date range

01/01/2018 -07/01/2018​

Name 1's total time is 07:00:00

Any help would be much appreciated.

Thanks in advance.

I've used Sumifs, Hlookup, sumproduct but no success.
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

Try

=SUMPRODUCT(('Sheet 2'!$A$2:$A$6=$A6)*('Sheet 2'!$B$1:$J$1>=$A$2)*('Sheet 2'!$B$1:$J$1<=$B$2),'Sheet 2'!$B$2:$J$6)

 

[Shan9]

Thanks a lot "Tetra201" it worked.

 

[Tetra201]

Glad to have helped.