Schedule Heatmap creation

[matmakay]

I am trying to sum up the number of people working in a specific time interval and day.. But I cannot seem to get my formula to work.

Column A = Name (Manual input)
Column B:H is days they are working (Manual input)
Column I:J Start/End times (Manual input)

I need formula in Columns M:U

Help!

=SUMIFS(B4:B38,I4:I38,"=>"&L9,J4:J38,"<"&L10)

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3	Name	Sun	Mon	Tue	Wed	Thu	Fri	Sat	START	STOP			Sun	Mon	Tue	Wed	Thu	Fri	Sat
4	Bob		1	1	1	1	1		8:00 AM	5:00 PM		6:00 AM	0	0	0	0	0	0	0
5	Sues			1	1	1	1	1	7:00 AM	4:00 PM		6:30 AM	0	0	0	0	0	0	0
6	Larry	1	1	1	1	1			8:00 AM	5:00 PM		7:00 AM	0	0	1	1	1	1	1
7	Cory			1	1	1	1	1	8:00 AM	5:00 PM		7:30 AM	0	0	1	1	1	1	1
8												8:00 AM	1	2	4	4	4	3	2
9												8:30 AM	1	2	4	4	4	3	2
10												9:00 AM	1	2	4	4	4	3	2
11												9:30 AM	1	2	4	4	4	3	2
12												10:00 AM	1	2	4	4	4	3	2
13												10:30 AM	1	2	4	4	4	3	2
14												11:00 AM	1	2	4	4	4	3	2
15												11:30 AM	1	2	4	4	4	3	2
16												12:00 PM	1	2	4	4	4	3	2
17												12:30 PM	1	2	4	4	4	3	2
18												1:00 PM	1	2	4	4	4	3	2
19												1:30 PM	1	2	4	4	4	3	2
20												2:00 PM	1	2	4	4	4	3	2
21												2:30 PM	1	2	4	4	4	3	2
22												3:00 PM	1	2	4	4	4	3	2
23												3:30 PM	1	2	4	4	4	3	2
24												4:00 PM	1	2	3	3	3	2	1
25												4:30 PM	1	2	3	3	3	2	1
26												5:00 PM	0	0	0	0	0	0	0
27												5:30 PM	0	0	0	0	0	0	0
28												6:00 PM
Sheet24

Cell Formulas
Range	Formula
M4:S27	M4	=SUMPRODUCT(B$4:B$7,($L5<=$J$4:$J$7)*($L4>=$I$4:$I$7))

 

[matmakay]

It worked THANK YOU !! Except for First interval - I expanded for full 24 hours.

 

[matmakay]

i had to adjust midnight to 12:00:01 am to make that interval work

 

[Eric W]

I'm glad it works for you! I can't explain why the 1st interval didn't work. I tried it on mine, and it worked ok. It sounds like you got a workaround anyway.

Good luck! ?