Excel Pivot table Time Values is not populating same as Source Sheet

Krish9999

New Member
Joined
Dec 27, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello Excel Experts, I am not able to Populate Time Values from my source sheet. Even I need to Sum up Time values in Pivot Table to show grand total. can you please help me or guide me to achieve this. very thank you in Advance
YearMonthAgent NameInbound HandledOutbound HandledInbound AHTOutbound AHTAvg Hold TimeUnavailable TimeLogin TimeACW TimeRefusedAvailable TimeHold TimeConsult TimeInbound Conference TimeInbound Active Talk TimeInbound Hold TimeOutbound Handle TimeActive Talk TimeHandle TimeWorking TimeOccupancyInbound Handle TimeSpeed Of Answer
2023November
A​
020:00:0000:21:490:00:0000:05:4500:48:1900:01:2900:00:000:00:000:00:000:00:000:00:000:00:0000:43:3800:42:0900:43:3800:44:03100.00%0:00:000:00:00
2023November
B​
2604000:15:2600:05:280:00:0043:21:00169:26:4718:27:1650:00:000:00:000:00:000:00:000:00:0010:30:3403:38:4041:26:2570:31:18144:33:0348.86%66:52:3700:12:15
2023November
B​
3151200:12:1300:04:560:00:0027:21:59162:21:2108:11:37078:00:590:00:000:00:000:00:000:00:000:00:0000:59:0956:55:2265:06:59143:10:5945.49%64:07:4900:08:42
2023November
C​
170400:14:4700:05:0300:01:4730:35:36154:20:0203:55:19085:24:0200:14:130:00:000:00:0037:51:4900:14:1300:20:1138:05:0042:14:32127:39:4433.09%41:54:2100:04:50
2023November
C​
260100:12:5000:04:380:00:0036:27:19175:24:5508:28:57091:47:070:00:000:00:000:00:0047:10:070:00:0000:04:3847:10:1755:39:14147:26:3337.75%55:34:3600:07:28
2023November
D​
16100:10:1100:03:0600:02:43137:49:26141:14:2301:08:39001:47:2200:10:520:00:000:00:0001:24:1300:10:5200:03:0601:26:3702:46:0804:33:3660.74%02:43:0200:00:23
2023November
D​
178000:08:500:00:000:00:0023:07:58107:27:4602:28:58160:35:490:00:000:00:000:00:0023:43:530:00:000:00:0023:43:5326:12:5086:48:4530.20%26:12:5000:06:13
2023November
E​
297100:11:2500:02:3400:02:3635:16:49173:58:5313:04:48295:10:5401:57:160:00:000:00:0041:32:3501:57:1600:02:3441:33:3856:35:42151:46:5237.29%56:33:0800:10:41
2023November
E​
224900:13:0000:10:180:00:0042:17:49149:43:4221:39:043678:58:560:00:000:00:000:00:0027:28:540:00:0001:32:4628:24:5950:04:03129:04:5738.80%48:31:1700:36:37
2023November
F​
201100:12:1000:18:160:00:0053:17:49143:44:5314:21:18063:44:110:00:000:00:000:00:0026:28:060:00:0000:18:1626:42:4041:03:58104:48:2239.18%40:45:4200:07:05
2023November
F​
185900:13:2200:04:5100:02:1827:22:53131:57:3108:15:07070:50:0801:02:060:00:000:00:0032:23:0101:02:0600:43:3732:40:2241:57:35112:49:4437.20%41:13:5700:05:42
2023November
G​
290400:12:1800:05:1100:02:5828:21:44173:08:0408:46:09093:45:2402:52:320:00:000:00:0047:53:5502:52:3200:20:4448:07:4159:46:23153:32:2938.93%59:25:3900:09:40
2023November
G​
4000:09:570:00:0000:02:0000:55:2002:14:0200:06:06000:44:5900:02:000:00:000:00:0000:31:4300:02:000:00:0000:31:4300:39:5001:24:4846.97%00:39:5000:00:08
2023November
H​
2861100:12:0600:05:3100:03:4024:38:23167:58:0207:54:20092:28:1202:48:230:00:000:00:0047:16:2502:45:5001:00:4047:57:5458:40:36151:13:5938.82%57:39:5700:09:17
2023November
H​
5100:11:0200:04:1100:01:48132:25:53133:05:1900:23:44000:03:2500:03:360:00:000:00:0000:29:2500:03:3600:04:1100:32:0000:59:2001:03:1094.56%00:55:0900:00:08
2023November
I​
3002100:11:0900:03:3800:02:2134:17:14173:40:0206:18:08188:24:2200:07:0200:11:130:00:0049:50:1900:07:0201:16:1950:34:5957:00:09145:40:5639.20%55:43:5100:09:29
2023November
I​
217300:09:3600:06:3800:04:1651:06:03167:48:4614:46:1810396:26:1403:58:580:00:000:00:0016:08:2303:53:3000:19:5316:17:0135:02:18131:29:0126.65%34:42:2501:25:31
2023November
J​
2000:13:580:00:000:00:00156:23:14157:04:5100:12:53900:25:260:00:0000:01:080:00:0000:15:020:00:000:00:0000:15:0200:27:5500:54:3052.32%00:27:5500:07:22
2023November
J​
3100:10:1700:05:110:00:00150:33:04152:07:1400:18:281701:16:120:00:000:00:000:00:0000:16:400:00:0000:05:1100:17:3400:36:0201:52:3832.11%00:30:5100:18:06
2023November
K​
3452600:08:1300:02:2900:01:0726:30:03160:54:1403:23:51089:20:5700:20:020:00:000:00:0043:52:5800:20:0201:04:4444:37:5248:21:45137:48:0235.12%47:17:0100:10:06
2023November
K​
11100:12:2300:00:540:00:00162:29:24165:10:1200:23:41000:47:090:00:000:00:000:00:0001:52:360:00:0000:00:5401:53:3002:17:1203:04:2974.42%02:16:1800:00:17
2023November
L​
2541600:14:0100:07:3200:00:2227:59:05149:28:5607:31:23067:35:4500:01:060:00:000:00:0052:27:3300:01:0602:00:3053:48:5161:21:20129:01:1447.58%59:20:5000:07:50
2023November
L​
1481000:18:3800:10:1900:04:3822:00:44108:24:5808:04:57046:49:1000:04:380:00:000:00:0038:12:0400:04:3801:43:0539:32:1647:41:5194:29:1150.46%45:58:4600:04:27
2023November
M​
294600:09:3700:04:1000:04:5150:14:0099:25:3617:34:3717319:12:0105:53:550:00:000:00:0023:56:4605:53:5500:25:0224:03:2547:31:5766:46:1371.23%47:06:5602:22:17
2023November
M​
1936400:13:0800:06:5000:03:5838:21:09162:26:3304:00:15078:16:2400:03:580:00:000:00:0039:06:0400:03:5807:17:2345:26:3749:30:49128:05:3938.75%42:13:2600:05:57
2023November
N​
11500:12:4900:01:3400:03:01110:46:34112:44:4700:45:20000:13:0100:06:030:00:000:00:0001:32:0700:06:0300:07:4901:37:2302:28:4602:43:3391.95%02:20:5700:00:16
2023November
N​
2721600:14:1300:06:4900:03:2054:50:14179:23:5923:31:24081:45:3005:19:130:00:000:00:0036:34:5705:18:3801:48:5937:26:2766:17:04148:05:0944.77%64:28:0500:07:34
2023November
O​
3591300:09:1800:03:4100:01:3236:48:21174:43:2811:45:51193:09:4600:06:070:00:000:00:0044:18:3800:06:0700:47:5344:34:1156:26:09149:40:5837.72%55:38:1600:10:50
2023November
O​
3572200:11:1300:06:5000:02:2833:27:40175:52:4012:42:54085:47:0900:19:460:00:000:00:0054:24:2600:19:4602:30:1456:12:2269:15:03155:07:5544.67%66:44:4800:09:16
2023November
P​
174300:11:5700:11:5300:03:0438:13:41120:57:2811:42:543259:12:2400:09:120:00:000:00:0022:51:3600:09:1200:35:3823:21:4535:13:5194:26:4137.31%34:38:1300:32:11
2023November
P​
233200:10:5900:06:4900:03:2018:58:46117:10:1204:14:11059:31:1500:40:000:00:000:00:0037:48:4800:40:0000:13:3837:59:4142:53:52102:25:3741.88%42:40:1400:06:20
2023November
Q​
000:00:000:00:000:00:0021:53:2223:47:140:00:0000:00:000:00:0001:53:520:00:000:00:000:00:000:00:000:00:000:00:0001:53:520.00%0:00:000:00:00
2023November
Q​
2231100:14:500:00:000:00:0036:48:56152:23:300:00:0000:00:000:00:000:00:000:00:0040:01:0004:27:5101:33:0041:00:5856:41:40126:40:4444.77%55:08:4000:07:20
2023November
R​
60000:16:240:00:000:00:0008:07:1738:27:3703:57:38217:53:350:00:000:00:000:00:0012:26:430:00:000:00:0012:26:4316:24:2034:17:5847.83%16:24:2000:03:35
2023November
R​
248300:11:2900:08:070:00:0038:25:32161:13:1107:19:50282:14:410:00:000:00:000:00:0040:12:020:00:0000:24:2240:31:4247:51:31130:07:2936.79%47:27:0900:09:34
2023November
S​
2564100:14:5300:07:1000:05:1644:53:11173:47:2617:44:11578:57:0201:50:2700:10:520:00:0046:05:5101:50:2704:53:4248:47:4768:22:26146:38:2746.41%63:28:4300:10:43
2023November
S​
262600:11:1800:04:5900:01:5831:40:46146:06:0610:14:06574:45:2000:03:570:00:000:00:0039:25:2800:03:5700:29:5539:33:0849:51:11124:39:2640.01%49:21:1600:11:30
2023November
T​
241900:09:3500:02:080:00:0033:28:16148:33:1107:35:03083:48:480:00:000:00:000:00:0031:12:150:00:0000:19:1031:13:4938:48:52122:39:5831.65%38:29:4200:08:21
2023November
T​
351500:12:3400:06:0500:00:01156:46:32171:53:2401:48:53007:59:5300:00:010:00:000:00:0005:55:1600:00:0101:31:0907:01:5308:50:4716:55:4452.52%07:19:3800:00:58
2023November
U​
244000:06:500:00:0000:01:3719:27:27105:58:3005:39:15164:21:3300:06:260:00:000:00:0022:02:5500:06:260:00:0022:02:5527:48:3692:10:1730.17%27:48:3600:08:49
2023November
U​
172100:18:4100:12:1200:06:4830:12:42129:20:1514:39:291059:59:5911:39:490:00:000:00:0027:22:0111:39:4900:12:1227:27:3253:46:50113:47:0247.27%53:34:3800:12:38
2023November
V​
2302200:12:0300:08:2400:02:2935:31:17119:35:5918:22:34853:05:4202:23:490:00:000:00:0027:32:3502:23:4903:04:3728:31:0049:17:23102:27:1648.14%46:12:4600:16:08
2023November
V​
3633600:09:3000:02:440:00:0029:02:06171:18:2208:10:34591:06:390:00:000:00:000:00:0049:50:170:00:0001:38:2550:59:2059:09:53150:26:5039.37%57:31:2800:14:18
2023November
W​
24400:09:2400:15:5400:03:35119:19:02127:29:1702:00:41105:20:1100:35:510:00:000:00:0001:50:4300:35:5101:03:3702:12:4604:49:1810:10:5647.47%03:45:4100:01:23
2023November
W​
166400:09:2300:01:3600:03:1921:19:2881:59:3604:09:14138:44:2001:22:500:00:000:00:0020:28:3401:22:5000:06:2420:31:3926:03:4364:49:2240.22%25:57:1800:06:11
2023November
X​
2761500:11:2000:02:5600:01:2436:00:42170:40:0510:19:29092:03:5101:07:160:00:000:00:0041:10:4901:07:1600:44:0041:23:0552:49:49144:58:5136.46%52:05:4900:09:05
2023November
X​
114700:10:3700:06:280:00:00139:52:06147:02:4100:40:43000:38:500:00:000:00:000:00:0001:38:480:00:0005:04:1806:20:1907:01:0207:51:1891.56%01:56:4400:00:16
2023November
Y​
201900:14:5500:13:4200:03:1951:43:10142:45:0419:22:41458:20:4500:33:140:00:000:00:0031:10:0600:33:1002:03:1632:05:2652:01:20110:24:3447.14%49:58:0400:09:47
2023November
Y​
321900:06:4600:05:3800:00:0734:18:05143:16:0101:46:10073:38:4500:00:200:00:000:00:0034:29:1000:00:2000:50:4335:16:3737:03:06110:44:0633.47%36:12:2300:11:08
2023November
Z​
2691200:12:5200:06:5100:02:3430:15:50147:29:4116:28:25474:34:5802:44:390:00:000:00:0039:30:1502:44:3901:22:0839:50:0359:03:07133:42:1644.19%57:41:0000:11:21
2023November
Z​
266600:12:1200:09:3400:02:3655:16:28152:08:1824:58:21366:44:2801:33:200:00:000:00:0028:08:2701:33:2000:57:2328:31:4355:03:24121:50:1145.20%54:06:0100:11:28
2023November
[​
226100:10:0200:13:2900:05:0327:56:28150:45:3607:19:321892:06:2600:40:270:00:000:00:0029:48:4800:40:2700:13:2930:02:0038:01:58130:08:3929.22%37:48:3000:21:16
2023November
[​
164400:13:5500:02:3300:04:1940:41:51157:14:4610:26:41088:44:2908:29:120:00:000:00:0019:15:4608:29:1200:10:1019:17:3838:13:30126:59:3630.11%38:03:2000:04:27
2023November
\​
000:00:000:00:000:00:0000:05:1100:05:110:00:0000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000:00:000.00%0:00:000:00:00
2023November
\​
196700:13:030:00:000:00:0035:04:31160:03:010:00:0000:00:0001:31:220:00:000:00:0032:39:3101:31:2200:51:3033:13:0543:29:07133:43:1032.53%42:37:3700:05:36
2023November
]​
291500:12:1100:02:3500:02:1326:45:25176:45:1206:14:17096:53:1101:26:320:00:000:00:0051:31:3701:26:3200:12:5451:38:0659:18:56156:14:0437.97%59:06:0200:07:30
2023November
]​
315900:12:5200:06:5300:05:5959:03:10155:45:4223:41:44051:47:4401:05:500:00:000:00:0043:36:0401:05:5001:02:0143:46:3868:34:12120:24:1656.97%67:32:1100:10:06
2023November
^​
6100:19:3400:10:2600:03:57180:17:00181:57:0300:43:19100:15:2600:03:570:00:000:00:0001:10:2900:03:5700:10:2601:20:3302:07:4902:23:2289.23%01:57:2200:00:53
2023November
^​
254000:07:570:00:0000:01:5050:45:34177:15:1013:03:260105:52:3903:57:240:00:000:00:0016:39:2403:57:240:00:0016:39:2433:40:13139:33:0224.13%33:40:1300:08:45
2023November
_​
2561300:16:3200:06:4500:02:3940:29:10164:25:2322:55:38174:50:0004:22:080:00:000:00:0043:54:5504:22:0601:27:4444:41:4571:59:31146:51:5149.03%70:31:4700:09:06
2023November
_​
2151000:16:5700:07:0600:04:4643:16:12154:38:1515:48:39265:13:1004:17:370:00:000:00:0041:15:1904:17:3701:11:0141:48:2361:54:38127:10:4248.70%60:43:3700:10:34
2023November
`​
162700:14:5500:05:2200:02:55159:08:22165:44:4500:27:14000:32:1600:14:340:00:000:00:0003:28:3400:09:4402:24:5105:41:4506:23:3407:03:3792.24%03:58:4300:00:26
2023November
`​
3000:13:250:00:000:00:0005:46:4306:20:4200:16:07000:09:500:00:000:00:000:00:0000:24:090:00:000:00:0000:24:0900:40:1600:50:0680.37%00:40:1600:00:06
2023November
a​
286500:12:1600:06:0200:04:0237:50:26169:00:5610:43:58482:55:3304:50:450:00:000:00:0043:06:1304:50:4500:30:0843:23:2358:58:06141:54:2841.56%58:27:5800:11:07
2023November
a​
243200:10:1700:09:1400:04:1731:14:13148:51:5715:48:443691:29:4802:04:180:00:000:00:0023:58:0402:04:1800:18:2724:02:4541:55:47133:26:2731.43%41:37:2000:36:39
2023November
b​
3051300:12:4400:10:3000:00:0247:55:37174:56:0821:05:12081:00:3900:00:020:00:000:00:0044:52:4400:00:0202:16:2645:55:5167:01:05148:05:4445.27%64:44:3900:08:55
2023November
b​
3061000:11:1600:05:520:00:0032:15:12179:12:2508:39:12197:05:110:00:000:00:000:00:0048:52:350:00:0000:58:4049:49:3158:28:43155:36:2637.59%57:30:0300:10:02
2023November
c​
377500:09:1500:01:5700:01:2345:14:06172:46:4710:12:47179:29:1100:04:080:00:000:00:0047:54:3500:04:0800:09:4547:58:0158:14:55137:45:2842.29%58:05:1100:14:47
 

Attachments

  • Capture.JPG
    Capture.JPG
    197.8 KB · Views: 10

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
I can't see how youre displaying the format for the SUM of time.
But, the SUM seems to work for me:

Book2
ABCDEFGH
1YearMonthAgent NameSpeed Of Answer
22023.00NovemberA00:00:00
32023.00NovemberB00:12:15
42023.00NovemberB00:08:42
52023.00NovemberC00:04:50
62023.00NovemberC00:07:28
72023.00NovemberD00:00:23
82023.00NovemberD00:06:13
92023.00NovemberE00:10:41
102023.00NovemberE00:36:37
112023.00NovemberF00:07:05
122023.00NovemberF00:05:42
132023.00NovemberG00:09:40
142023.00NovemberG00:00:08
152023.00NovemberH00:09:17
162023.00NovemberH00:00:08
172023.00NovemberI00:09:29
182023.00NovemberI01:25:31
192023.00NovemberJ00:07:22
202023.00NovemberJ00:18:06
212023.00NovemberK00:10:06
222023.00NovemberK00:00:17
232023.00NovemberL00:07:50
242023.00NovemberL00:04:27
252023.00NovemberM02:22:17
262023.00NovemberM00:05:57
272023.00NovemberN00:00:16
282023.00NovemberN00:07:34
292023.00NovemberO00:10:50
302023.00NovemberO00:09:16
312023.00NovemberP00:32:11
322023.00NovemberP00:06:20
332023.00NovemberQ00:00:00
342023.00NovemberQ00:07:20
352023.00NovemberR00:03:35
362023.00NovemberR00:09:34
372023.00NovemberS00:10:43
382023.00NovemberS00:11:30
392023.00NovemberT00:08:21
402023.00NovemberT00:00:58
412023.00NovemberU00:08:49
422023.00NovemberU00:12:38
432023.00NovemberV00:16:08
442023.00NovemberV00:14:18
452023.00NovemberW00:01:23
462023.00NovemberW00:06:11
472023.00NovemberX00:09:05
482023.00NovemberX00:00:16
492023.00NovemberY00:09:47
502023.00NovemberY00:11:08
512023.00NovemberZ00:11:21
522023.00NovemberZ00:11:28
532023.00November[00:21:16
542023.00November[00:04:27
552023.00November\00:00:00
562023.00November\00:05:36
572023.00November]00:07:30
582023.00November]00:10:06
592023.00November^00:00:53
602023.00November^00:08:45
612023.00November_00:09:06
622023.00November_00:10:34
632023.00November`00:00:26
642023.00November`00:00:06
652023.00NovemberAa00:11:07
662023.00NovemberAa00:36:39
672023.00NovemberBb00:08:55
682023.00NovemberBb00:10:02
692023.00NovemberCc00:14:47
70
71
72
73Agent NameRow LabelsSum of Speed Of AnswerCheck
74A00:00:00[00:25:43TRUE
75Aa00:47:46\00:05:36TRUE
76B00:20:57]00:17:36TRUE
77Bb00:18:57^00:09:38TRUE
78C00:12:18_00:19:40TRUE
79Cc00:14:47`00:00:32TRUE
80D00:06:36A00:00:00TRUE
81E00:47:18Aa00:47:46TRUE
82F00:12:47B00:20:57TRUE
83G00:09:48Bb00:18:57TRUE
84H00:09:25C00:12:18TRUE
85I01:35:00Cc00:14:47TRUE
86J00:25:28D00:06:36TRUE
87K00:10:23E00:47:18TRUE
88L00:12:17F00:12:47TRUE
89M02:28:14G00:09:48TRUE
90N00:07:50H00:09:25TRUE
91O00:20:06I01:35:00TRUE
92P00:38:31J00:25:28TRUE
93Q00:07:20K00:10:23TRUE
94R00:13:09L00:12:17TRUE
95S00:22:13M02:28:14TRUE
96T00:09:19N00:07:50TRUE
97U00:21:27O00:20:06TRUE
98V00:30:26P00:38:31TRUE
99W00:07:34Q00:07:20TRUE
100X00:09:21R00:13:09TRUE
101Y00:20:55S00:22:13TRUE
102Z00:22:49T00:09:19TRUE
103[00:25:43U00:21:27TRUE
104\00:05:36V00:30:26TRUE
105]00:17:36W00:07:34TRUE
106^00:09:38X00:09:21TRUE
107_00:19:40Y00:20:55TRUE
108`00:00:32Z00:22:49TRUE
109Grand Total13:21:46
110
Sheet1
Cell Formulas
RangeFormula
B74:B108B74=SUMIF($C$2:$C$69,A74,$D$2:$D$69)
G74:G108G74=ROUND(INDEX($B$74:$B$108,MATCH(D74,$A$74:$A$108,0)),6)=ROUND(E74,6)
 
Upvote 0

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top