Sum data between specific day and opening/closing times

[Thoron6]

OK so I think I'm making this more complicated than it should be...

I have a list of data like the below (Table 2) and a separate table with the code opening and closing times which vary by day of the week (Table 1).


I'm looking to get a list of unique codes with the totals falling between the opening and closing times and a separate column for data that falls outside the opening and closing times each day. Eg.

Code | Total between times/day | Total outside of opening times.
123 xxx xxx
456 xxx xxx

Any help with the formula would be greatly appreciated.

Thanks

Table 1

Code

Mon

Tue

Wed

Thur

Fri

Sat

Sun

Mon

Tue

Wed

Thur

Fri

Sat

Sun

123

10:00

10:00

10:00

10:00

10:00

09:00

16:00

22:00

22:00

22:00

22:00

23:00

23:30

22:00

456

10:00

10:00

10:00

10:00

10:00

09:00

11:00

22:00

22:00

22:00

22:00

23:00

23:30

22:00

789

11:00

11:00

11:00

11:00

11:00

09:00

12:00

22:00

22:00

22:00

22:00

23:00

23:30

22:00

<tbody>
</tbody>

Table 2

Code	Day	00:30	01:00	01:30	02:00	02:30	03:00	03:30	04:00	04:30
123	Mon	2.6	2.6	2.8	1.8	1.7	2.0	3.1	4.2	1.7
123	Tue	2.3	3	6	7	7	8	11	10	7
123	Wed	2	4	5	7	8	7	15	8	2
456	Mon	3	4	5	9	8	6	14	8	3
456	Tue	3	3	4	7	8	9	26	8	2
456	Wed	2	3	6	5	8	10	20	8	1
789	Mon	2	3	4	5	5	10	31	20	6
789	Tue	2	3	5	2	5	18	12	4	6
789	Wed	2	3	6	5	4	10	21	4	6

<tbody>
</tbody>

 

[Eric W]

Actually, that might be as complicated as you think it is!

Here's what I came up with:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Code	Mon	Tue	Wed	Thur	Fri	Sat	Sun	Mon	Tue	Wed	Thur	Fri	Sat	Sun
2	123	1:30	10:00	10:00	10:00	10:00	9:00	16:00	22:00	22:00	22:00	22:00	23:00	23:30	22:00
3	456	10:00	10:00	10:00	10:00	10:00	9:00	11:00	22:00	22:00	22:00	22:00	23:00	23:30	22:00
4	789	11:00	11:00	3:00	11:00	11:00	9:00	12:00	22:00	22:00	22:00	22:00	23:00	23:30	22:00
5
6
7	Code	Day	0:30	1:00	1:30	2:00	2:30	3:00	3:30	4:00	4:30
8	123	Mon	2.6	2.6	2.8	1.8	1.7	2	3.1	4.2	1.7
9	123	Tue	2.3	3	6	7	7	8	11	10	7
10	123	Wed	2	4	5	7	8	7	15	8	2
11	456	Mon	3	4	5	9	8	6	14	8	3
12	456	Tue	3	3	4	7	8	9	26	8	2
13	456	Wed	2	3	6	5	8	10	20	8	1
14	789	Mon	2	3	4	5	5	10	31	20	6
15	789	Tue	2	3	5	2	5	18	12	4	6
16	789	Wed	2	3	6	5	4	10	21	4	6
17
18
19	Code	Total between times/day	Total outside of opening times
20	123	17.3	124.5
21	456	0	193
22	789	41	163

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula C20 =SUMPRODUCT($C$8:$K$16*($A$8:$A$16=$A20))-B20 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B20 {=SUMPRODUCT(($C$8:$K$16)*($A$8:$A$16=$A20)*($C$7:$K$7>=SUBTOTAL(9,OFFSET($B$2,MATCH($A20,$A$2:$A$4,0)-1,MATCH($B$8:$B$16,$B$1:$H$1,0)-1)))*($C$7:$K$7<=SUBTOTAL(9,OFFSET($I$2,MATCH($A20,$A$2:$A$4,0)-1,MATCH($B$8:$B$16,$I$1:$O$1,0)-1))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Change your references to match your workbook. You can see that I've changed a few times for testing. Let me know how this works.

 

[Thoron6]

Thank you Eric,

I'm not getting an error with the above but I am getting 0's for everything. I have adapted to my Spread sheet as per the below:

{=SUMPRODUCT(('Profile data'!$G$2:$BB$22815)*('Profile data'!$B$2:$B$22815=Summary!$A2)*('Profile data'!$G$1:$BB$1>=SUBTOTAL(9,OFFSET('NEW 12-12-16'!$C$3,MATCH(Summary!$A2,'NEW 12-12-16'!$A$3:$A$1745,0)-1,MATCH('Profile data'!$F$2:$F$22815,'NEW 12-12-16'!$C$2:$I$2,0)-1)))*('Profile data'!$G$1:$BB$1<=SUBTOTAL(9,OFFSET('NEW 12-12-16'!$J$3,MATCH(Summary!$A2,'NEW 12-12-16'!$A$3:$A$1745,0)-1,MATCH('Profile data'!$F$2:$F$22815,'NEW 12-12-16'!$J$2:$P$2,0)-1))))}

I'm wondering if it's because the cell referencing the Monday opening time (I2 in your data) is locked? However, this should still work and bring data back for one day. I will keep trying because I don't think it's too far off at all but all are returning "0" at present.

Thank you!

 

[Eric W]

I haven't tested your version yet, but it appears to be a match for mine, with the ranges adapted. The locked cell shouldn't matter. Are you entering the formula as an array formula? By which I mean you are entering it using Control+Shift+Enter, and not just manually entering the {} ?

 

[Thoron6]

Hi Eric, Yes, I am putting it as an array formula. If the start time is locked, will it take into account different opening times for other properties or all follow the same opening/closing times? I believe it was like for like so can't understand the "0" result but I'll try again later to make sure it is the same. Were you getting the correct results on your example?

Thanks again

 

[Eric W]

The formula should take into account different starting times for each property, regardless of whether the start time is locked. Yes, I did get correct results on my example. I've tried rebuilding your formula, splitting the parts among 3 sheets like you did, but I'm having trouble lining things up. Could you post what the first dozen lines or so from each sheet look like?

 

[Thoron6]

Thank you Eric,

Sorry it's been a while but I had a look and it wasn't working because the time formats were not the same. After making sure they were the same format the formula worked fine. Thank you for your help!

 

[Eric W]

Glad it works. Thanks for the update.