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)

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F	G	H	I
1	Employee ID	Amount 1	Amount 2	Sum Amount1	Sum Amount2
2	ABCD	4939.44	4939.44	127797.1	126297.1		ABCD	127797.1	126297.1
3	ABCD	5389.44	5389.44				EFGH	197029.7	197029.7
4	ABCD	5239.44	4939.44				A1234	490409.4	490409.4
5	ABCD	4939.44	4939.44				B1234	85145.19	85145.19
6	ABCD	5239.44	4939.44
7	ABCD	5389.44	5389.44
8	ABCD	4939.44	4939.44
9	ABCD	5839.44	5839.44
10	ABCD	4939.44	4939.44
11	ABCD	5389.44	5389.44
12	ABCD	4939.44	4939.44
13	ABCD	5389.44	5389.44
14	ABCD	5239.44	4939.44
15	ABCD	4939.44	4939.44
16	ABCD	4939.44	4939.44
17	ABCD	4939.44	4939.44
18	ABCD	5239.44	4939.44
19	ABCD	4939.44	4939.44
20	ABCD	4738.07	4738.07
21	ABCD	10546.83	10546.83
22	ABCD	4738.07	4738.07
23	ABCD	5188.07	5188.07
24	ABCD	5038.07	4738.07
25	ABCD	4738.07	4738.07
26	EFGH	9274.85	9274.85
27	EFGH	6745.35	6745.35
28	EFGH	6745.35	6745.35
29	EFGH	2529.5	2529.5
30	EFGH	6745.35	6745.35
31	EFGH	6855.38	6855.38
32	EFGH	6745.35	6745.35
33	EFGH	9274.85	9274.85
34	EFGH	6745.35	6745.35
35	EFGH	9274.85	9274.85
36	EFGH	6745.35	6745.35
37	EFGH	10286.66	10286.66
38	EFGH	6745.35	6745.35
39	EFGH	9274.85	9274.85
40	EFGH	6745.35	6745.35
41	EFGH	9274.85	9274.85
42	EFGH	6745.35	6745.35
43	EFGH	14550.32	14550.32
44	EFGH	6745.35	6745.35
45	EFGH	8708.78	8708.78
46	EFGH	6333.66	6333.66
47	EFGH	8708.78	8708.78
48	EFGH	6333.66	6333.66
49	EFGH	12561.57	12561.57
50	EFGH	6333.66	6333.66
51	A1234	17708.34	17708.34
52	A1234	17708.34	17708.34
53	A1234	17708.34	17708.34
54	A1234	17708.34	17708.34
55	A1234	17708.34	17708.34
56	A1234	17708.34	17708.34
57	A1234	17708.34	17708.34
58	A1234	17708.34	17708.34
59	A1234	17708.34	17708.34
60	A1234	17708.34	17708.34
61	A1234	17708.34	17708.34
62	A1234	17708.34	17708.34
63	A1234	17708.34	17708.34
64	A1234	17708.34	17708.34
65	A1234	17708.34	17708.34
66	A1234	17708.34	17708.34
67	A1234	17708.34	17708.34
68	A1234	17708.34	17708.34
69	A1234	83117.59	83117.59
70	A1234	17708.34	17708.34
71	A1234	17708.34	17708.34
72	A1234	17708.34	17708.34
73	A1234	17708.34	17708.34
74	A1234	17708.34	17708.34
75	B1234	3428.75	3428.75
76	B1234	3428.75	3428.75
77	B1234	3428.75	3428.75
78	B1234	3428.75	3428.75
79	B1234	3428.75	3428.75
80	B1234	3428.75	3428.75
81	B1234	3428.75	3428.75
82	B1234	3428.75	3428.75
83	B1234	3428.75	3428.75
84	B1234	3428.75	3428.75
85	B1234	3428.75	3428.75
86	B1234	3428.75	3428.75
87	B1234	3428.75	3428.75
88	B1234	3428.75	3428.75
89	B1234	3428.75	3428.75
90	B1234	3428.75	3428.75
91	B1234	3428.75	3428.75
92	B1234	3428.75	3428.75
93	B1234	3250	3250
94	B1234	7177.69	7177.69
95	B1234	3250	3250
96	B1234	3250	3250
97	B1234	3250	3250
98	B1234	3250	3250
Sheet1

Cell Formulas
Range	Formula
D2:E2	D2	=SUM(B2:B25)
G2:G5	G2	=UNIQUE(A2:A98)
H2:H5	H2	=SUM(FILTER($B$2:$B$98,$G2=$A$2:$A$98,""))
I2:I5	I2	=SUM(FILTER($C$2:$C$98,$G2=$A$2:$A$98,""))
Dynamic array formulas.

 

[DJunqueira]

You could use this formula.

=SUMIF($A$2:$A$98,$A2,$B$2:$B$98)

And for a spill array result.

=SUMIF($A$2:$A$98,A2:A98,$B$2:$B$98)

 

[Michael M]

Why not use Subtotals.....
Data Tab>>Outline>>Subtotals>>>and then select these options

 

[zinah]

try this:

Book1
	A	B	C	D	E	F	G	H	I
1	Employee ID	Amount 1	Amount 2	Sum Amount1	Sum Amount2
2	ABCD	4939.44	4939.44	127797.1	126297.1		ABCD	127797.1	126297.1
3	ABCD	5389.44	5389.44				EFGH	197029.7	197029.7
4	ABCD	5239.44	4939.44				A1234	490409.4	490409.4
5	ABCD	4939.44	4939.44				B1234	85145.19	85145.19
6	ABCD	5239.44	4939.44
7	ABCD	5389.44	5389.44
8	ABCD	4939.44	4939.44
9	ABCD	5839.44	5839.44
10	ABCD	4939.44	4939.44
11	ABCD	5389.44	5389.44
12	ABCD	4939.44	4939.44
13	ABCD	5389.44	5389.44
14	ABCD	5239.44	4939.44
15	ABCD	4939.44	4939.44
16	ABCD	4939.44	4939.44
17	ABCD	4939.44	4939.44
18	ABCD	5239.44	4939.44
19	ABCD	4939.44	4939.44
20	ABCD	4738.07	4738.07
21	ABCD	10546.83	10546.83
22	ABCD	4738.07	4738.07
23	ABCD	5188.07	5188.07
24	ABCD	5038.07	4738.07
25	ABCD	4738.07	4738.07
26	EFGH	9274.85	9274.85
27	EFGH	6745.35	6745.35
28	EFGH	6745.35	6745.35
29	EFGH	2529.5	2529.5
30	EFGH	6745.35	6745.35
31	EFGH	6855.38	6855.38
32	EFGH	6745.35	6745.35
33	EFGH	9274.85	9274.85
34	EFGH	6745.35	6745.35
35	EFGH	9274.85	9274.85
36	EFGH	6745.35	6745.35
37	EFGH	10286.66	10286.66
38	EFGH	6745.35	6745.35
39	EFGH	9274.85	9274.85
40	EFGH	6745.35	6745.35
41	EFGH	9274.85	9274.85
42	EFGH	6745.35	6745.35
43	EFGH	14550.32	14550.32
44	EFGH	6745.35	6745.35
45	EFGH	8708.78	8708.78
46	EFGH	6333.66	6333.66
47	EFGH	8708.78	8708.78
48	EFGH	6333.66	6333.66
49	EFGH	12561.57	12561.57
50	EFGH	6333.66	6333.66
51	A1234	17708.34	17708.34
52	A1234	17708.34	17708.34
53	A1234	17708.34	17708.34
54	A1234	17708.34	17708.34
55	A1234	17708.34	17708.34
56	A1234	17708.34	17708.34
57	A1234	17708.34	17708.34
58	A1234	17708.34	17708.34
59	A1234	17708.34	17708.34
60	A1234	17708.34	17708.34
61	A1234	17708.34	17708.34
62	A1234	17708.34	17708.34
63	A1234	17708.34	17708.34
64	A1234	17708.34	17708.34
65	A1234	17708.34	17708.34
66	A1234	17708.34	17708.34
67	A1234	17708.34	17708.34
68	A1234	17708.34	17708.34
69	A1234	83117.59	83117.59
70	A1234	17708.34	17708.34
71	A1234	17708.34	17708.34
72	A1234	17708.34	17708.34
73	A1234	17708.34	17708.34
74	A1234	17708.34	17708.34
75	B1234	3428.75	3428.75
76	B1234	3428.75	3428.75
77	B1234	3428.75	3428.75
78	B1234	3428.75	3428.75
79	B1234	3428.75	3428.75
80	B1234	3428.75	3428.75
81	B1234	3428.75	3428.75
82	B1234	3428.75	3428.75
83	B1234	3428.75	3428.75
84	B1234	3428.75	3428.75
85	B1234	3428.75	3428.75
86	B1234	3428.75	3428.75
87	B1234	3428.75	3428.75
88	B1234	3428.75	3428.75
89	B1234	3428.75	3428.75
90	B1234	3428.75	3428.75
91	B1234	3428.75	3428.75
92	B1234	3428.75	3428.75
93	B1234	3250	3250
94	B1234	7177.69	7177.69
95	B1234	3250	3250
96	B1234	3250	3250
97	B1234	3250	3250
98	B1234	3250	3250
Sheet1

Cell Formulas
Range	Formula
D2:E2	D2	=SUM(B2:B25)
G2:G5	G2	=UNIQUE(A2:A98)
H2:H5	H2	=SUM(FILTER($B$2:$B$98,$G2=$A$2:$A$98,""))
I2:I5	I2	=SUM(FILTER($C$2:$C$98,$G2=$A$2:$A$98,""))
Dynamic array formulas.

Thanks a lot for your suggestion! Is there a way we can use the unique and sum in one formula? This way I will not create another table with only the employee ID and both amounts? As the original table has more than 10 columns.

 

[zinah]

You could use this formula.



And for a spill array result.

I liked your suggested formula, but what I need is to get one sum for all the multiple employee ID, then I will pivot the sum columns to get the total amount of all unique employee IDs.

 

[zinah]

Why not use Subtotals.....
Data Tab>>Outline>>Subtotals>>>and then select these options

View attachment 107933

Thank you for your suggestion, this can work, however, what I'm looking to get as an end result is like below table, the sum amounts of each employee (amount 1 and 2) and check if there's any match, with your suggestion I don't think it will work:

	Sum Amount1	Sum Amount2	Match?​
ABCD​	127797.1​	126297.1​	FALSE​
EFGH​	197029.72​	197029.72​	TRUE​
A1234​	490409.41​	490409.41​	TRUE​
B1234​	85145.19​	85145.19​	TRUE​

 

[DJunqueira]

For this final result you can use the formula bellow:

=LET(u,UNIQUE(A2:A98),b,SUMIF(A2:A98,u,B2:B98),c,SUMIF(A2:A98,u,C2:C98),HSTACK(u,b,c,b=c))

 

[alansidman]

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

 

[zinah]

For this final result you can use the formula bellow:

I LOVE IT! Thanks for the great formula!