SUMIF By time range

[Browneh89]

Hello all,

I am attempting to use a SUMIF formula to calculate the number of units collected received between 3 shift patterns on a worksheet. I have attempted this myself but keep getting a 0 returned.

The time values I'm using as a range are actually a formula looking at another sheet splitting it from its date so I'm not sure if this could be a factor?

This is what I'm working with below I would appreciate any help offered!

Template 2.xlsx
	A	B	C	D	E
1			22:00-06:00	06:00-14:00	14:00-22:00
2			0	0	0
3
4	Time	Units
5	01:00	48
6	05:00	26
7	06:00	52
8	06:10	5
9	06:20	11
10	07:00	46
11	07:30	4
12	07:40	28
13	08:00	52
14	08:20	16
15	08:40	16
16	09:00	28
17	09:10	5
18	09:20	28
19	09:40	26
20	09:50	11
21	10:00	29
22	10:10	9
23	10:20	20
24	10:30	2
25	10:40	7
26	10:50	7
27	11:00	28
28	11:10	8
29	11:20	52
30	11:40	15
31	11:50	1
32	12:10	2
33	12:30	3
34	12:50	8
35	13:00	26
36	13:20	26
37	14:00	27
38	14:10	3
39	14:20	57
40	14:40	12
41	15:00	29
42	15:10	6
43	15:20	20
44	15:40	26
45	16:00	28
46	16:20	20
47	16:40	26
48	17:00	29
49	17:20	20
50	17:30	1
51	18:00	23
52	20:00	27
53	20:30	8
54	21:00	26
55	23:00	29
Sheet1

Cell Formulas
Range	Formula
C2	C2	=SUMIFS($B$5:$B$98,$A$5:$A$98,">="&#REF!,$A$5:$A$98,"<"&A1)
D2	D2	=SUMIFS($B$5:$B$98,$A$5:$A$98,">="&A1,$A$5:$A$98,"<"&TIME(HOUR(A1),MINUTE(A1)+480,0))
E2	E2	=SUMIFS($B$5:$B$98,$A$5:$A$98,">="&#REF!,$A$5:$A$98,"<"&TIME(HOUR(#REF!),MINUTE(#REF!)+480,0))
A5:A55	A5	=RIGHT('sheet 2'!A2,5)

Named Ranges
Name	Refers To	Cells
Print_Area	=Sheet1!$A$1:$J$43	C2:D2

 

[jasonb75]

Your times in column A are text, try changing the formula to

=RIGHT('sheet 2'!A2,5)+0

Also you have several #REF! errors in your formulas which means that they are referring to ranges that have been deleted.

 

[Browneh89]

Your times in column A are text, try changing the formula to

=RIGHT('sheet 2'!A2,5)+0

Also you have several #REF! errors in your formulas which means that they are referring to ranges that have been deleted.

Ahh yeah ignore thoes i didn't realize they would flag. They where failed attempts at the formula even before I deleted the columns which caused the REF error. I have changed the formatting of the column to time with no effect.

 

[jasonb75]

I had to overwrite the formulas in column A with the actual times as I don't have the source that they refer to, but this should work.

Note that I've assumed (for example) that 06:00 in column A should be counted in the 06:00-14:00 window, not the 22:00-06:00 window.

Book1
	A	B	C	D	E
1			22:00-06:00	06:00-14:00	14:00-22:00
2			103	571	388
3
4	Time	Units
5	01:00	48
6	05:00	26
7	06:00	52
8	06:10	5
9	06:20	11
10	07:00	46
11	07:30	4
12	07:40	28
13	08:00	52
14	08:20	16
15	08:40	16
16	09:00	28
17	09:10	5
18	09:20	28
19	09:40	26
20	09:50	11
21	10:00	29
22	10:10	9
23	10:20	20
24	10:30	2
25	10:40	7
26	10:50	7
27	11:00	28
28	11:10	8
29	11:20	52
30	11:40	15
31	11:50	1
32	12:10	2
33	12:30	3
34	12:50	8
35	13:00	26
36	13:20	26
37	14:00	27
38	14:10	3
39	14:20	57
40	14:40	12
41	15:00	29
42	15:10	6
43	15:20	20
44	15:40	26
45	16:00	28
46	16:20	20
47	16:40	26
48	17:00	29
49	17:20	20
50	17:30	1
51	18:00	23
52	20:00	27
53	20:30	8
54	21:00	26
55	23:00	29
Sheet10

Cell Formulas
Range	Formula
C2	C2	=SUMIFS($B$5:$B$55,$A$5:$A$55,">="&TIME(22,0,0))+SUMIFS($B$5:$B$55,$A$5:$A$55,"<"&TIME(6,0,0))
D2	D2	=SUMIFS($B$5:$B$55,$A$5:$A$55,">="&TIME(6,0,0),$A$5:$A$55,"<"&TIME(14,0,0))
E2	E2	=SUMIFS($B$5:$B$55,$A$5:$A$55,">="&TIME(14,0,0),$A$5:$A$55,"<"&TIME(22,0,0))