Sum of multiple rows for each employee

[zinah]

Hi,
I have below sheet that has multiple rows for each employee ID and what I need is to get a proper formula to sum amount 1 and sum amount 2 for each employee, can you help pls?

Book2
	A	B	C	D	E
1	Employee ID	Amount 1	Amount 2	Sum Amount1	Sum Amount2
2	ABCD	4,939.44	4,939.44	127,797.10	126,297.10
3	ABCD	5,389.44	5,389.44
4	ABCD	5,239.44	4,939.44
5	ABCD	4,939.44	4,939.44
6	ABCD	5,239.44	4,939.44
7	ABCD	5,389.44	5,389.44
8	ABCD	4,939.44	4,939.44
9	ABCD	5,839.44	5,839.44
10	ABCD	4,939.44	4,939.44
11	ABCD	5,389.44	5,389.44
12	ABCD	4,939.44	4,939.44
13	ABCD	5,389.44	5,389.44
14	ABCD	5,239.44	4,939.44
15	ABCD	4,939.44	4,939.44
16	ABCD	4,939.44	4,939.44
17	ABCD	4,939.44	4,939.44
18	ABCD	5,239.44	4,939.44
19	ABCD	4,939.44	4,939.44
20	ABCD	4,738.07	4,738.07
21	ABCD	10,546.83	10,546.83
22	ABCD	4,738.07	4,738.07
23	ABCD	5,188.07	5,188.07
24	ABCD	5,038.07	4,738.07
25	ABCD	4,738.07	4,738.07
26	EFGH	9,274.85	9,274.85
27	EFGH	6,745.35	6,745.35
28	EFGH	6,745.35	6,745.35
29	EFGH	2,529.50	2,529.50
30	EFGH	6,745.35	6,745.35
31	EFGH	6,855.38	6,855.38
32	EFGH	6,745.35	6,745.35
33	EFGH	9,274.85	9,274.85
34	EFGH	6,745.35	6,745.35
35	EFGH	9,274.85	9,274.85
36	EFGH	6,745.35	6,745.35
37	EFGH	10,286.66	10,286.66
38	EFGH	6,745.35	6,745.35
39	EFGH	9,274.85	9,274.85
40	EFGH	6,745.35	6,745.35
41	EFGH	9,274.85	9,274.85
42	EFGH	6,745.35	6,745.35
43	EFGH	14,550.32	14,550.32
44	EFGH	6,745.35	6,745.35
45	EFGH	8,708.78	8,708.78
46	EFGH	6,333.66	6,333.66
47	EFGH	8,708.78	8,708.78
48	EFGH	6,333.66	6,333.66
49	EFGH	12,561.57	12,561.57
50	EFGH	6,333.66	6,333.66
51	A1234	17,708.34	17,708.34
52	A1234	17,708.34	17,708.34
53	A1234	17,708.34	17,708.34
54	A1234	17,708.34	17,708.34
55	A1234	17,708.34	17,708.34
56	A1234	17,708.34	17,708.34
57	A1234	17,708.34	17,708.34
58	A1234	17,708.34	17,708.34
59	A1234	17,708.34	17,708.34
60	A1234	17,708.34	17,708.34
61	A1234	17,708.34	17,708.34
62	A1234	17,708.34	17,708.34
63	A1234	17,708.34	17,708.34
64	A1234	17,708.34	17,708.34
65	A1234	17,708.34	17,708.34
66	A1234	17,708.34	17,708.34
67	A1234	17,708.34	17,708.34
68	A1234	17,708.34	17,708.34
69	A1234	83,117.59	83,117.59
70	A1234	17,708.34	17,708.34
71	A1234	17,708.34	17,708.34
72	A1234	17,708.34	17,708.34
73	A1234	17,708.34	17,708.34
74	A1234	17,708.34	17,708.34
75	B1234	3,428.75	3,428.75
76	B1234	3,428.75	3,428.75
77	B1234	3,428.75	3,428.75
78	B1234	3,428.75	3,428.75
79	B1234	3,428.75	3,428.75
80	B1234	3,428.75	3,428.75
81	B1234	3,428.75	3,428.75
82	B1234	3,428.75	3,428.75
83	B1234	3,428.75	3,428.75
84	B1234	3,428.75	3,428.75
85	B1234	3,428.75	3,428.75
86	B1234	3,428.75	3,428.75
87	B1234	3,428.75	3,428.75
88	B1234	3,428.75	3,428.75
89	B1234	3,428.75	3,428.75
90	B1234	3,428.75	3,428.75
91	B1234	3,428.75	3,428.75
92	B1234	3,428.75	3,428.75
93	B1234	3,250.00	3,250.00
94	B1234	7,177.69	7,177.69
95	B1234	3,250.00	3,250.00
96	B1234	3,250.00	3,250.00
97	B1234	3,250.00	3,250.00
98	B1234	3,250.00	3,250.00
Sheet4

Cell Formulas
Range	Formula
D2:E2	D2	=SUM(B2:B25)

 

[zinah]

Here is an alternative solution with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", type text}, {"Amount 1", type number}, {"Amount 2", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee ID"}, {{"Total 1", each List.Sum([Amount 1]), type nullable number}, {"Total 2", each List.Sum([Amount 2]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Match", each if [Total 1]=[Total 2] then "TRUE" else "FALSE")
in
    #"Added Custom"

Book1
	E	F	G	H
1	Employee ID	Total 1	Total 2	Match
2	ABCD	127797.1	126297.1	FALSE
3	EFGH	197029.72	197029.72	TRUE
4	A1234	490409.41	490409.41	TRUE
5	B1234	85145.19	85145.19	TRUE
Sheet1

Now I know something new about Power Query! Thank you so much for the great example and solution!

 

[DJunqueira]

Tks for the feedback, glad to have helped.