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!
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 |