to sum time basis date

[vinod9111]

Hi All,

Looking your help for constructing the formula. I have a data which looks like below . In the date field there are dates and time
[TABLE="width: 240"]
<tbody>[TR]
[TD] Date[/TD]
[TD] Time spent in minutes[/TD]
[/TR]
[TR]
[TD]11/06/2017 10:16[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/06/2017 10:15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/06/2017 10:14[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/06/2017 10:11[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/06/2017 9:42[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/06/2017 9:33[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/06/2017 9:24[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/06/2017 9:17[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/04/2017 19:06[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/04/2017 16:52[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/04/2017 16:52[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/04/2017 16:42[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/04/2017 16:42[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/04/2017 16:42[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The out put sheet has below format , I need to sum up the time basis the time slab given for the date which I need, please help me construct the formula
[TABLE="width: 259"]
<tbody>[TR]
[TD="colspan: 2"]Time slab[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]9:30 AM[/TD]
[TD]10:30 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:31 AM[/TD]
[TD]11:30 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11:31 AM[/TD]
[TD]12:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:31 PM[/TD]
[TD]1:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1:31 PM[/TD]
[TD]2:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2:31 PM[/TD]
[TD]3:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3:31 PM[/TD]
[TD]4:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4:31 PM[/TD]
[TD]5:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5:31 PM[/TD]
[TD]6:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6:31 PM[/TD]
[TD]7:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7:31 PM[/TD]
[TD]8:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:31 PM[/TD]
[TD]9:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:31 PM[/TD]
[TD]10:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:31 PM[/TD]
[TD]11:30 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11:31 PM[/TD]
[TD]12:30 AM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

regards
Vinod

 

[kvsrinivasamurthy]

Sheet2 C1=date.
Eg: C1=11/7/2017

Formula in Sheet2 in C2, then drag down.

=SUMPRODUCT((Sheet1!$A$2:$A$15>=Sheet2!$C$1+Sheet2!A2)*(Sheet1!$A$2:$A$15<=Sheet2!$C$1+Sheet2!B2)*Sheet1!$B$2:$B$15)

 

[vinod9111]

Thanks KVSrinivasamurthy for your instant reply.

But sumproduct makes my pc slow, is there any other alternative formula to retrieve the desired result.

regards.

Vinod

 

[kvsrinivasamurthy]

Try this

=SUMIFS(Sheet1!$B$2:$B$15,Sheet1!$A$2:$A$15,">="&Sheet2!$C$1+Sheet2!A2,Sheet1!$A$2:$A$15,"<="&Sheet2!$C$1+Sheet2!B2)