Total Time (hours) by ID

[zinah]

Hi,

I have below sample table that has Time and Employee ID, what I need is to calculate total hours viewed by individual ID. I sorted the ID already but in real data I have thousands of rows without any kind of sort and I need to make it easy for me to calculate time spent by employee every month. Can you advise any formula that works to resolve this?

Book1
	A	B	C
1	Viewed (Hours)	ID	Time Spent by ID
2	0.2656	2001086121524
3	1.0656	2001086121524
4	0.0022	2001088121524
5	0.6344	2000905421524
6	0.4231	2000999921524
7	0.4989	2000928321524
8	0.1506	2000973221524
9	0.695	2001250321524
10	0.7111	2001185921524
11	0.0042	2001014521524
12	3.9639	2001047621524
13	0.6006	2000972221524
14	1.0414	2000885421524
15	1.9431	2000879121524
16	0.4856	2000476821524
17	0.1067	1158249621524
18	0.0064	2001107721524
19	0.0006	2000929821524
20	0.0047	2001086521524
21	0.5778	2001155621524
22	1.0158	2001239621524
23	0.0464	2000967021524
24	0.0606	2000927421524
25	0.0053	2000999121524
26	0.0647	2000969721524
27	0.6931	2001272821524
28	0.3556	2001016621524
29	0.705	1175122921524
30	0.5086	2001152821524
31	0.8175	2001103521524
32	0.0161	2001091221524
33	1.9183	2000870321524
34	1.58	2000870321524
35	0.6325	2000420321524
36	0.0172	2000284921524
37	0.0011	1162481421524
38	0.0103	2001120521524
39	0.3922	2000873621524
40	0.0206	2001019921524
41	0.0008	2001149821524
42	0.0414	2000982621524
43	0.0297	1171293521524
44	0.0017	2001224221524
45	0.0344	2001185621524
46	0.0008	2000967021524
47	0.0008	1163631521524
48	0.1917	2001088521524
49	0.1947	2001117921524
50	0.0075	1163968321524
51	0.0231	2001091721524
52	0.0006	2000831721524
53	0.0414	2001087921524
54	0.2042	2001005021524
55	0.0083	2001168021524
56	0.0025	2001092021524
57	0.005	2001050521524
58	0.1703	2001171321524
59	0.0394	2000978421524
60	0.0006	2000967021524
61	1.3883	2001114121524
62	0.4006	2000940421524
63	0.0014	1163629221524
64	0.0003	2001161821524
65	0.0142	2000977121524
66	1.1222	2001213421524
67	0.5272	1154539121524
68	0.0222	2001070821524
69	0.1036	2001096621524
70	0.4208	2001060621524
71	0.0572	2001082221524
72	0.0086	2001016921524
73	0.0006	2001078621524
74	0.0578	2001096621524
75	0.0364	2001223821524
76	0.6761	2001006621524
77	0.0094	2001089921524
78	0.0217	2001038921524
79	0.0008	2001093421524
80	0.0025	2001019921524
81	2.5131	2001038921524
82	0.2064	2001211521524
83	2.6697	2001075221524
84	0.2156	2000935621524
85	2.2125	2001121821524
86	0.0467	2000772821524
87	0.0006	2001105221524
88	0.0394	2001046121524
89	0.0003	1155950321524
90	0.1	2001046121524
91	2.0389	2001243621524
92	0.0789	2001091521524
93	0.0197	2001224221524
94	0.0103	2001038421524
95	5.0883	1154218921524
96	0.0203	2000923821524
97	0.0181	2001038421524
98	0.0422	2001019521524
99	0.0108	2000946321524
100	1.0433	2000711221524
101	0.1964	2000476821524
102	0.0003	2000923821524
103	0.3172	2001110621524
104	0.0122	2001045121524
105	0.5067	1154434621524
106	3.1158	2001046421524
107	0.7847	2001075221524
108	0.0019	2001055321524
109	2.6344	2001098621524
110	1.8617	2001106321524
111	1.0103	2001126121524
112	0.3822	2001111821524
113	0.0022	2001045121524
114	0.0106	2001045121524
115	0.0644	2001050521524
116	0.0556	2000865721524
117	0.0064	2000982621524
118	0.4519	1055350821524
119	0.255	1055350821524
120	0.3644	1055350821524
121	0.1819	1055350821524
122	0.0547	1055350821524
123	0.5028	1055350821524
124	0.7458	1055350821524
125	0.0044	1055350821524
126	0.3719	1055350821524
127	0.0003	1055350821524
128	2.0294	1055350821524
129	0.3919	1055350821524
130	0.0011	1055350821524
131	0.6875	1055350821524
132	0.0044	1055350821524
133	0.1097	1055350821524
134	1.6756	1055350821524
135	0.0333	1055350821524
136	0.5186	1055350821524
137	0.0606	1055350821524
138	0.0844	1055350821524
139	0.7764	1055350821524
140	0.0003	1055350821524
141	0.4547	1055350821524
142	0.5472	1055350821524
Sheet1

 

[etaf]

you could use a pivot table to sum the hours

Book1
	A	B	C	D	E	F
1	Viewed (Hours)	ID	Time Spent by ID
2	0.2656	2001086121524			Row Labels	Sum of Viewed (Hours)
3	1.0656	2001086121524			1055350821524	10.3082
4	0.0022	2001088121524			1154218921524	5.0883
5	0.6344	2000905421524			1154434621524	0.5067
6	0.4231	2000999921524			1154539121524	0.5272
7	0.4989	2000928321524			1155950321524	0.0003
8	0.1506	2000973221524			1158249621524	0.1067
9	0.695	2001250321524			1162481421524	0.0011
10	0.7111	2001185921524			1163629221524	0.0014
11	0.0042	2001014521524			1163631521524	0.0008
12	3.9639	2001047621524			1163968321524	0.0075
13	0.6006	2000972221524			1171293521524	0.0297
14	1.0414	2000885421524			1175122921524	0.705
15	1.9431	2000879121524			2000284921524	0.0172
16	0.4856	2000476821524			2000420321524	0.6325
17	0.1067	1158249621524			2000476821524	0.682
18	0.0064	2001107721524			2000711221524	1.0433
19	0.0006	2000929821524			2000772821524	0.0467
20	0.0047	2001086521524			2000831721524	0.0006
21	0.5778	2001155621524			2000865721524	0.0556
22	1.0158	2001239621524			2000870321524	3.4983
23	0.0464	2000967021524			2000873621524	0.3922
24	0.0606	2000927421524			2000879121524	1.9431
25	0.0053	2000999121524			2000885421524	1.0414
26	0.0647	2000969721524			2000905421524	0.6344
27	0.6931	2001272821524			2000923821524	0.0206
28	0.3556	2001016621524			2000927421524	0.0606
29	0.705	1175122921524			2000928321524	0.4989
30	0.5086	2001152821524			2000929821524	0.0006
31	0.8175	2001103521524			2000935621524	0.2156
32	0.0161	2001091221524			2000940421524	0.4006
33	1.9183	2000870321524			2000946321524	0.0108
34	1.58	2000870321524			2000967021524	0.0478
35	0.6325	2000420321524			2000969721524	0.0647
36	0.0172	2000284921524			2000972221524	0.6006
37	0.0011	1162481421524			2000973221524	0.1506
38	0.0103	2001120521524			2000977121524	0.0142
39	0.3922	2000873621524			2000978421524	0.0394
40	0.0206	2001019921524			2000982621524	0.0478
41	0.0008	2001149821524			2000999121524	0.0053
42	0.0414	2000982621524			2000999921524	0.4231
43	0.0297	1171293521524			2001005021524	0.2042
44	0.0017	2001224221524			2001006621524	0.6761
45	0.0344	2001185621524			2001014521524	0.0042
46	0.0008	2000967021524			2001016621524	0.3556
47	0.0008	1163631521524			2001016921524	0.0086
48	0.1917	2001088521524			2001019521524	0.0422
49	0.1947	2001117921524			2001019921524	0.0231
50	0.0075	1163968321524			2001038421524	0.0284
51	0.0231	2001091721524			2001038921524	2.5348
52	0.0006	2000831721524			2001045121524	0.025
53	0.0414	2001087921524			2001046121524	0.1394
54	0.2042	2001005021524			2001046421524	3.1158
55	0.0083	2001168021524			2001047621524	3.9639
56	0.0025	2001092021524			2001050521524	0.0694
57	0.005	2001050521524			2001055321524	0.0019
58	0.1703	2001171321524			2001060621524	0.4208
59	0.0394	2000978421524			2001070821524	0.0222
60	0.0006	2000967021524			2001075221524	3.4544
61	1.3883	2001114121524			2001078621524	0.0006
62	0.4006	2000940421524			2001082221524	0.0572
63	0.0014	1163629221524			2001086121524	1.3312
64	0.0003	2001161821524			2001086521524	0.0047
65	0.0142	2000977121524			2001087921524	0.0414
66	1.1222	2001213421524			2001088121524	0.0022
67	0.5272	1154539121524			2001088521524	0.1917
68	0.0222	2001070821524			2001089921524	0.0094
69	0.1036	2001096621524			2001091221524	0.0161
70	0.4208	2001060621524			2001091521524	0.0789
71	0.0572	2001082221524			2001091721524	0.0231
72	0.0086	2001016921524			2001092021524	0.0025
73	0.0006	2001078621524			2001093421524	0.0008
74	0.0578	2001096621524			2001096621524	0.1614
75	0.0364	2001223821524			2001098621524	2.6344
76	0.6761	2001006621524			2001103521524	0.8175
77	0.0094	2001089921524			2001105221524	0.0006
78	0.0217	2001038921524			2001106321524	1.8617
79	0.0008	2001093421524			2001107721524	0.0064
80	0.0025	2001019921524			2001110621524	0.3172
81	2.5131	2001038921524			2001111821524	0.3822
82	0.2064	2001211521524			2001114121524	1.3883
83	2.6697	2001075221524			2001117921524	0.1947
84	0.2156	2000935621524			2001120521524	0.0103
85	2.2125	2001121821524			2001121821524	2.2125
86	0.0467	2000772821524			2001126121524	1.0103
87	0.0006	2001105221524			2001149821524	0.0008
88	0.0394	2001046121524			2001152821524	0.5086
89	0.0003	1155950321524			2001155621524	0.5778
90	0.1	2001046121524			2001161821524	0.0003
91	2.0389	2001243621524			2001168021524	0.0083
92	0.0789	2001091521524			2001171321524	0.1703
93	0.0197	2001224221524			2001185621524	0.0344
94	0.0103	2001038421524			2001185921524	0.7111
95	5.0883	1154218921524			2001211521524	0.2064
96	0.0203	2000923821524			2001213421524	1.1222
97	0.0181	2001038421524			2001223821524	0.0364
98	0.0422	2001019521524			2001224221524	0.0214
99	0.0108	2000946321524			2001239621524	1.0158
100	1.0433	2000711221524			2001243621524	2.0389
101	0.1964	2000476821524			2001250321524	0.695
102	0.0003	2000923821524			2001272821524	0.6931
103	0.3172	2001110621524			(blank)
104	0.0122	2001045121524			Grand Total	65.5595
105	0.5067	1154434621524
106	3.1158	2001046421524
107	0.7847	2001075221524
108	0.0019	2001055321524
109	2.6344	2001098621524
110	1.8617	2001106321524
111	1.0103	2001126121524
112	0.3822	2001111821524
113	0.0022	2001045121524
114	0.0106	2001045121524
115	0.0644	2001050521524
116	0.0556	2000865721524
117	0.0064	2000982621524
118	0.4519	1055350821524
119	0.255	1055350821524
120	0.3644	1055350821524
121	0.1819	1055350821524
122	0.0547	1055350821524
123	0.5028	1055350821524
124	0.7458	1055350821524
125	0.0044	1055350821524
126	0.3719	1055350821524
127	0.0003	1055350821524
128	2.0294	1055350821524
129	0.3919	1055350821524
130	0.0011	1055350821524
131	0.6875	1055350821524
132	0.0044	1055350821524
133	0.1097	1055350821524
Sheet1

 

[alansidman]

An alternative means to the end is Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Sum Viewed", each List.Sum([#"Viewed (Hours)"]), type number}})
in
    #"Grouped Rows"

 

[jtakw]

Hi,

Just throwing in a formula option.
Column C will SUM total hours for each unique ID for the 1st instance in column B, additional instances will be Blank, you can then filter column C for blanks, if you like.

Book3.xlsx
	A	B	C
1	Viewed (Hours)	ID	Time Spent by ID
2	0.2656	2001086121524	1.3312
3	1.0656	2001086121524
4	0.0022	2001088121524	0.0022
5	0.6344	2000905421524	0.6344
6	0.4231	2000999921524	0.4231
7	0.4989	2000928321524	0.4989
8	0.1506	2000973221524	0.1506
9	0.695	2001250321524	0.695
10	0.7111	2001185921524	0.7111
11	0.0042	2001014521524	0.0042
12	3.9639	2001047621524	3.9639
13	0.6006	2000972221524	0.6006
14	1.0414	2000885421524	1.0414
15	1.9431	2000879121524	1.9431
16	0.4856	2000476821524	0.682
17	0.1067	1158249621524	0.1067
18	0.0064	2001107721524	0.0064
19	0.0006	2000929821524	0.0006
20	0.0047	2001086521524	0.0047
21	0.5778	2001155621524	0.5778
22	1.0158	2001239621524	1.0158
23	0.0464	2000967021524	0.0478
24	0.0606	2000927421524	0.0606
25	0.0053	2000999121524	0.0053
26	0.0647	2000969721524	0.0647
27	0.6931	2001272821524	0.6931
28	0.3556	2001016621524	0.3556
29	0.705	1175122921524	0.705
30	0.5086	2001152821524	0.5086
31	0.8175	2001103521524	0.8175
32	0.0161	2001091221524	0.0161
33	1.9183	2000870321524	3.4983
34	1.58	2000870321524
35	0.6325	2000420321524	0.6325
36	0.0172	2000284921524	0.0172
37	0.0011	1162481421524	0.0011
38	0.0103	2001120521524	0.0103
39	0.3922	2000873621524	0.3922
40	0.0206	2001019921524	0.0231
41	0.0008	2001149821524	0.0008
42	0.0414	2000982621524	0.0478
43	0.0297	1171293521524	0.0297
44	0.0017	2001224221524	0.0214
45	0.0344	2001185621524	0.0344
46	0.0008	2000967021524
47	0.0008	1163631521524	0.0008
48	0.1917	2001088521524	0.1917
49	0.1947	2001117921524	0.1947
50	0.0075	1163968321524	0.0075
51	0.0231	2001091721524	0.0231
52	0.0006	2000831721524	0.0006
53	0.0414	2001087921524	0.0414
54	0.2042	2001005021524	0.2042
55	0.0083	2001168021524	0.0083
56	0.0025	2001092021524	0.0025
57	0.005	2001050521524	0.0694
58	0.1703	2001171321524	0.1703
59	0.0394	2000978421524	0.0394
60	0.0006	2000967021524
61	1.3883	2001114121524	1.3883
62	0.4006	2000940421524	0.4006
63	0.0014	1163629221524	0.0014
64	0.0003	2001161821524	0.0003
65	0.0142	2000977121524	0.0142
66	1.1222	2001213421524	1.1222
67	0.5272	1154539121524	0.5272
68	0.0222	2001070821524	0.0222
69	0.1036	2001096621524	0.1614
70	0.4208	2001060621524	0.4208
71	0.0572	2001082221524	0.0572
72	0.0086	2001016921524	0.0086
73	0.0006	2001078621524	0.0006
74	0.0578	2001096621524
75	0.0364	2001223821524	0.0364
76	0.6761	2001006621524	0.6761
77	0.0094	2001089921524	0.0094
78	0.0217	2001038921524	2.5348
79	0.0008	2001093421524	0.0008
80	0.0025	2001019921524
81	2.5131	2001038921524
82	0.2064	2001211521524	0.2064
83	2.6697	2001075221524	3.4544
84	0.2156	2000935621524	0.2156
85	2.2125	2001121821524	2.2125
86	0.0467	2000772821524	0.0467
87	0.0006	2001105221524	0.0006
88	0.0394	2001046121524	0.1394
89	0.0003	1155950321524	0.0003
90	0.1	2001046121524
91	2.0389	2001243621524	2.0389
92	0.0789	2001091521524	0.0789
93	0.0197	2001224221524
94	0.0103	2001038421524	0.0284
95	5.0883	1154218921524	5.0883
96	0.0203	2000923821524	0.0206
97	0.0181	2001038421524
98	0.0422	2001019521524	0.0422
99	0.0108	2000946321524	0.0108
100	1.0433	2000711221524	1.0433
101	0.1964	2000476821524
102	0.0003	2000923821524
103	0.3172	2001110621524	0.3172
104	0.0122	2001045121524	0.025
105	0.5067	1154434621524	0.5067
106	3.1158	2001046421524	3.1158
107	0.7847	2001075221524
108	0.0019	2001055321524	0.0019
109	2.6344	2001098621524	2.6344
110	1.8617	2001106321524	1.8617
111	1.0103	2001126121524	1.0103
112	0.3822	2001111821524	0.3822
113	0.0022	2001045121524
114	0.0106	2001045121524
115	0.0644	2001050521524
116	0.0556	2000865721524	0.0556
117	0.0064	2000982621524
118	0.4519	1055350821524	10.3082
119	0.255	1055350821524
120	0.3644	1055350821524
121	0.1819	1055350821524
122	0.0547	1055350821524
123	0.5028	1055350821524
124	0.7458	1055350821524
125	0.0044	1055350821524
126	0.3719	1055350821524
127	0.0003	1055350821524
128	2.0294	1055350821524
129	0.3919	1055350821524
130	0.0011	1055350821524
131	0.6875	1055350821524
132	0.0044	1055350821524
133	0.1097	1055350821524
134	1.6756	1055350821524
135	0.0333	1055350821524
136	0.5186	1055350821524
137	0.0606	1055350821524
138	0.0844	1055350821524
139	0.7764	1055350821524
140	0.0003	1055350821524
141	0.4547	1055350821524
142	0.5472	1055350821524
Sheet871

Cell Formulas
Range	Formula
C2:C142	C2	=IF(COUNTIF(B$2:B2,B2)=1,SUMIF(B$2:B$200,B2,A$2:A$200),"")

 

[zinah]

you could use a pivot table to sum the hours

Book1
	A	B	C	D	E	F
1	Viewed (Hours)	ID	Time Spent by ID
2	0.2656	2001086121524			Row Labels	Sum of Viewed (Hours)
3	1.0656	2001086121524			1055350821524	10.3082
4	0.0022	2001088121524			1154218921524	5.0883
5	0.6344	2000905421524			1154434621524	0.5067
6	0.4231	2000999921524			1154539121524	0.5272
7	0.4989	2000928321524			1155950321524	0.0003
8	0.1506	2000973221524			1158249621524	0.1067
9	0.695	2001250321524			1162481421524	0.0011
10	0.7111	2001185921524			1163629221524	0.0014
11	0.0042	2001014521524			1163631521524	0.0008
12	3.9639	2001047621524			1163968321524	0.0075
13	0.6006	2000972221524			1171293521524	0.0297
14	1.0414	2000885421524			1175122921524	0.705
15	1.9431	2000879121524			2000284921524	0.0172
16	0.4856	2000476821524			2000420321524	0.6325
17	0.1067	1158249621524			2000476821524	0.682
18	0.0064	2001107721524			2000711221524	1.0433
19	0.0006	2000929821524			2000772821524	0.0467
20	0.0047	2001086521524			2000831721524	0.0006
21	0.5778	2001155621524			2000865721524	0.0556
22	1.0158	2001239621524			2000870321524	3.4983
23	0.0464	2000967021524			2000873621524	0.3922
24	0.0606	2000927421524			2000879121524	1.9431
25	0.0053	2000999121524			2000885421524	1.0414
26	0.0647	2000969721524			2000905421524	0.6344
27	0.6931	2001272821524			2000923821524	0.0206
28	0.3556	2001016621524			2000927421524	0.0606
29	0.705	1175122921524			2000928321524	0.4989
30	0.5086	2001152821524			2000929821524	0.0006
31	0.8175	2001103521524			2000935621524	0.2156
32	0.0161	2001091221524			2000940421524	0.4006
33	1.9183	2000870321524			2000946321524	0.0108
34	1.58	2000870321524			2000967021524	0.0478
35	0.6325	2000420321524			2000969721524	0.0647
36	0.0172	2000284921524			2000972221524	0.6006
37	0.0011	1162481421524			2000973221524	0.1506
38	0.0103	2001120521524			2000977121524	0.0142
39	0.3922	2000873621524			2000978421524	0.0394
40	0.0206	2001019921524			2000982621524	0.0478
41	0.0008	2001149821524			2000999121524	0.0053
42	0.0414	2000982621524			2000999921524	0.4231
43	0.0297	1171293521524			2001005021524	0.2042
44	0.0017	2001224221524			2001006621524	0.6761
45	0.0344	2001185621524			2001014521524	0.0042
46	0.0008	2000967021524			2001016621524	0.3556
47	0.0008	1163631521524			2001016921524	0.0086
48	0.1917	2001088521524			2001019521524	0.0422
49	0.1947	2001117921524			2001019921524	0.0231
50	0.0075	1163968321524			2001038421524	0.0284
51	0.0231	2001091721524			2001038921524	2.5348
52	0.0006	2000831721524			2001045121524	0.025
53	0.0414	2001087921524			2001046121524	0.1394
54	0.2042	2001005021524			2001046421524	3.1158
55	0.0083	2001168021524			2001047621524	3.9639
56	0.0025	2001092021524			2001050521524	0.0694
57	0.005	2001050521524			2001055321524	0.0019
58	0.1703	2001171321524			2001060621524	0.4208
59	0.0394	2000978421524			2001070821524	0.0222
60	0.0006	2000967021524			2001075221524	3.4544
61	1.3883	2001114121524			2001078621524	0.0006
62	0.4006	2000940421524			2001082221524	0.0572
63	0.0014	1163629221524			2001086121524	1.3312
64	0.0003	2001161821524			2001086521524	0.0047
65	0.0142	2000977121524			2001087921524	0.0414
66	1.1222	2001213421524			2001088121524	0.0022
67	0.5272	1154539121524			2001088521524	0.1917
68	0.0222	2001070821524			2001089921524	0.0094
69	0.1036	2001096621524			2001091221524	0.0161
70	0.4208	2001060621524			2001091521524	0.0789
71	0.0572	2001082221524			2001091721524	0.0231
72	0.0086	2001016921524			2001092021524	0.0025
73	0.0006	2001078621524			2001093421524	0.0008
74	0.0578	2001096621524			2001096621524	0.1614
75	0.0364	2001223821524			2001098621524	2.6344
76	0.6761	2001006621524			2001103521524	0.8175
77	0.0094	2001089921524			2001105221524	0.0006
78	0.0217	2001038921524			2001106321524	1.8617
79	0.0008	2001093421524			2001107721524	0.0064
80	0.0025	2001019921524			2001110621524	0.3172
81	2.5131	2001038921524			2001111821524	0.3822
82	0.2064	2001211521524			2001114121524	1.3883
83	2.6697	2001075221524			2001117921524	0.1947
84	0.2156	2000935621524			2001120521524	0.0103
85	2.2125	2001121821524			2001121821524	2.2125
86	0.0467	2000772821524			2001126121524	1.0103
87	0.0006	2001105221524			2001149821524	0.0008
88	0.0394	2001046121524			2001152821524	0.5086
89	0.0003	1155950321524			2001155621524	0.5778
90	0.1	2001046121524			2001161821524	0.0003
91	2.0389	2001243621524			2001168021524	0.0083
92	0.0789	2001091521524			2001171321524	0.1703
93	0.0197	2001224221524			2001185621524	0.0344
94	0.0103	2001038421524			2001185921524	0.7111
95	5.0883	1154218921524			2001211521524	0.2064
96	0.0203	2000923821524			2001213421524	1.1222
97	0.0181	2001038421524			2001223821524	0.0364
98	0.0422	2001019521524			2001224221524	0.0214
99	0.0108	2000946321524			2001239621524	1.0158
100	1.0433	2000711221524			2001243621524	2.0389
101	0.1964	2000476821524			2001250321524	0.695
102	0.0003	2000923821524			2001272821524	0.6931
103	0.3172	2001110621524			(blank)
104	0.0122	2001045121524			Grand Total	65.5595
105	0.5067	1154434621524
106	3.1158	2001046421524
107	0.7847	2001075221524
108	0.0019	2001055321524
109	2.6344	2001098621524
110	1.8617	2001106321524
111	1.0103	2001126121524
112	0.3822	2001111821524
113	0.0022	2001045121524
114	0.0106	2001045121524
115	0.0644	2001050521524
116	0.0556	2000865721524
117	0.0064	2000982621524
118	0.4519	1055350821524
119	0.255	1055350821524
120	0.3644	1055350821524
121	0.1819	1055350821524
122	0.0547	1055350821524
123	0.5028	1055350821524
124	0.7458	1055350821524
125	0.0044	1055350821524
126	0.3719	1055350821524
127	0.0003	1055350821524
128	2.0294	1055350821524
129	0.3919	1055350821524
130	0.0011	1055350821524
131	0.6875	1055350821524
132	0.0044	1055350821524
133	0.1097	1055350821524
Sheet1

Thanks for your input, pivot option would work fine, but I like formula options more

 

[zinah]

Hi,

Just throwing in a formula option.
Column C will SUM total hours for each unique ID for the 1st instance in column B, additional instances will be Blank, you can then filter column C for blanks, if you like.

Book3.xlsx
	A	B	C
1	Viewed (Hours)	ID	Time Spent by ID
2	0.2656	2001086121524	1.3312
3	1.0656	2001086121524
4	0.0022	2001088121524	0.0022
5	0.6344	2000905421524	0.6344
6	0.4231	2000999921524	0.4231
7	0.4989	2000928321524	0.4989
8	0.1506	2000973221524	0.1506
9	0.695	2001250321524	0.695
10	0.7111	2001185921524	0.7111
11	0.0042	2001014521524	0.0042
12	3.9639	2001047621524	3.9639
13	0.6006	2000972221524	0.6006
14	1.0414	2000885421524	1.0414
15	1.9431	2000879121524	1.9431
16	0.4856	2000476821524	0.682
17	0.1067	1158249621524	0.1067
18	0.0064	2001107721524	0.0064
19	0.0006	2000929821524	0.0006
20	0.0047	2001086521524	0.0047
21	0.5778	2001155621524	0.5778
22	1.0158	2001239621524	1.0158
23	0.0464	2000967021524	0.0478
24	0.0606	2000927421524	0.0606
25	0.0053	2000999121524	0.0053
26	0.0647	2000969721524	0.0647
27	0.6931	2001272821524	0.6931
28	0.3556	2001016621524	0.3556
29	0.705	1175122921524	0.705
30	0.5086	2001152821524	0.5086
31	0.8175	2001103521524	0.8175
32	0.0161	2001091221524	0.0161
33	1.9183	2000870321524	3.4983
34	1.58	2000870321524
35	0.6325	2000420321524	0.6325
36	0.0172	2000284921524	0.0172
37	0.0011	1162481421524	0.0011
38	0.0103	2001120521524	0.0103
39	0.3922	2000873621524	0.3922
40	0.0206	2001019921524	0.0231
41	0.0008	2001149821524	0.0008
42	0.0414	2000982621524	0.0478
43	0.0297	1171293521524	0.0297
44	0.0017	2001224221524	0.0214
45	0.0344	2001185621524	0.0344
46	0.0008	2000967021524
47	0.0008	1163631521524	0.0008
48	0.1917	2001088521524	0.1917
49	0.1947	2001117921524	0.1947
50	0.0075	1163968321524	0.0075
51	0.0231	2001091721524	0.0231
52	0.0006	2000831721524	0.0006
53	0.0414	2001087921524	0.0414
54	0.2042	2001005021524	0.2042
55	0.0083	2001168021524	0.0083
56	0.0025	2001092021524	0.0025
57	0.005	2001050521524	0.0694
58	0.1703	2001171321524	0.1703
59	0.0394	2000978421524	0.0394
60	0.0006	2000967021524
61	1.3883	2001114121524	1.3883
62	0.4006	2000940421524	0.4006
63	0.0014	1163629221524	0.0014
64	0.0003	2001161821524	0.0003
65	0.0142	2000977121524	0.0142
66	1.1222	2001213421524	1.1222
67	0.5272	1154539121524	0.5272
68	0.0222	2001070821524	0.0222
69	0.1036	2001096621524	0.1614
70	0.4208	2001060621524	0.4208
71	0.0572	2001082221524	0.0572
72	0.0086	2001016921524	0.0086
73	0.0006	2001078621524	0.0006
74	0.0578	2001096621524
75	0.0364	2001223821524	0.0364
76	0.6761	2001006621524	0.6761
77	0.0094	2001089921524	0.0094
78	0.0217	2001038921524	2.5348
79	0.0008	2001093421524	0.0008
80	0.0025	2001019921524
81	2.5131	2001038921524
82	0.2064	2001211521524	0.2064
83	2.6697	2001075221524	3.4544
84	0.2156	2000935621524	0.2156
85	2.2125	2001121821524	2.2125
86	0.0467	2000772821524	0.0467
87	0.0006	2001105221524	0.0006
88	0.0394	2001046121524	0.1394
89	0.0003	1155950321524	0.0003
90	0.1	2001046121524
91	2.0389	2001243621524	2.0389
92	0.0789	2001091521524	0.0789
93	0.0197	2001224221524
94	0.0103	2001038421524	0.0284
95	5.0883	1154218921524	5.0883
96	0.0203	2000923821524	0.0206
97	0.0181	2001038421524
98	0.0422	2001019521524	0.0422
99	0.0108	2000946321524	0.0108
100	1.0433	2000711221524	1.0433
101	0.1964	2000476821524
102	0.0003	2000923821524
103	0.3172	2001110621524	0.3172
104	0.0122	2001045121524	0.025
105	0.5067	1154434621524	0.5067
106	3.1158	2001046421524	3.1158
107	0.7847	2001075221524
108	0.0019	2001055321524	0.0019
109	2.6344	2001098621524	2.6344
110	1.8617	2001106321524	1.8617
111	1.0103	2001126121524	1.0103
112	0.3822	2001111821524	0.3822
113	0.0022	2001045121524
114	0.0106	2001045121524
115	0.0644	2001050521524
116	0.0556	2000865721524	0.0556
117	0.0064	2000982621524
118	0.4519	1055350821524	10.3082
119	0.255	1055350821524
120	0.3644	1055350821524
121	0.1819	1055350821524
122	0.0547	1055350821524
123	0.5028	1055350821524
124	0.7458	1055350821524
125	0.0044	1055350821524
126	0.3719	1055350821524
127	0.0003	1055350821524
128	2.0294	1055350821524
129	0.3919	1055350821524
130	0.0011	1055350821524
131	0.6875	1055350821524
132	0.0044	1055350821524
133	0.1097	1055350821524
134	1.6756	1055350821524
135	0.0333	1055350821524
136	0.5186	1055350821524
137	0.0606	1055350821524
138	0.0844	1055350821524
139	0.7764	1055350821524
140	0.0003	1055350821524
141	0.4547	1055350821524
142	0.5472	1055350821524
Sheet871

Cell Formulas
Range	Formula
C2:C142	C2	=IF(COUNTIF(B$2:B2,B2)=1,SUMIF(B$2:B$200,B2,A$2:A$200),"")

Thank you so much! The formula worked perfectly great

 

[zinah]

An alternative means to the end is Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Sum Viewed", each List.Sum([#"Viewed (Hours)"]), type number}})
in
    #"Grouped Rows"

Thank you so much for your input, I'm still new to Power Query and taking courses to enhance my level, but will definitely try your suggested solution once I'm done with my course

 

[jtakw]

Thank you so much! The formula worked perfectly great

You're welcome, thanks for the feedback.

 

[alansidman]

If you are at all interested in Power Query, then the book "M is for (Data) Monkey" is available in the MrE store. A great primer to learn PQ.