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

Date	Time spent in minutes
11/06/2017 10:16	2
11/06/2017 10:15	2
11/06/2017 10:14	2
11/06/2017 10:11	2
11/06/2017 9:42	2
11/06/2017 9:33	2
11/06/2017 9:24	2
11/06/2017 9:17	2
11/04/2017 19:06	2
11/04/2017 16:52	2
11/04/2017 16:52	2
11/04/2017 16:42	2
11/04/2017 16:42	2
11/04/2017 16:42

<tbody>
</tbody>

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

Time slab	Time
9:30 AM	10:30 AM
10:31 AM	11:30 AM
11:31 AM	12:30 PM
12:31 PM	1:30 PM
1:31 PM	2:30 PM
2:31 PM	3:30 PM
3:31 PM	4:30 PM
4:31 PM	5:30 PM
5:31 PM	6:30 PM
6:31 PM	7:30 PM
7:31 PM	8:30 PM
8:31 PM	9:30 PM
9:31 PM	10:30 PM
10:31 PM	11:30 PM
11:31 PM	12:30 AM

<tbody>
</tbody>

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)