Subtotal but need to include extra data

[jayhill]

Greetings All,
I have a sheet that contains employee data with a varying number of rows for each employee. I would like to do something analogous to a subtotal for each discrete SSN with a sum of each employee's gross amount however I need to also include the data in columns B through F in the subtotal line as well.

I'm sure I'm looking at this the wrong way. Ultimately, I would like to have just a single line per SSN that includes B through F as well as the SSN and a total of the gross amount for that SSN.

I'll bet that this is trivial for the gurus however I'm spinning my wheels. Any help greatly appreciated!


Excel 2016 (Windows) 32 bit

	B​	C​	D​	E​	F​	G​	H ​
1​	First Name	Last Name	Birthdate	Hire Date	Termination Date	Gross Amount​	Social Security Number
2​	Employee	One	1/1/1950	10/1/1984		1,000.00​	111-11-1111
3​	Employee	One	1/1/1950	10/1/1984		1,000.00​	111-11-1111
4​	Employee	One	1/1/1950	10/1/1984		1,000.00​	111-11-1111
5​	Employee	One	1/1/1950	10/1/1984		1,000.00​	111-11-1111
6​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
7​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
8​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
9​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
10​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
11​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
12​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
13​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
14​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
15​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333
16​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333
17​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333
18​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333
19​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333
20​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333

<tbody>
</tbody>

Sheet: test

<tbody>
</tbody>

 

[HockeyDiablo]

Greetings All,
I have a sheet that contains employee data with a varying number of rows for each employee. I would like to do something analogous to a subtotal for each discrete SSN with a sum of each employee's gross amount however I need to also include the data in columns B through F in the subtotal line as well.

I'm sure I'm looking at this the wrong way. Ultimately, I would like to have just a single line per SSN that includes B through F as well as the SSN and a total of the gross amount for that SSN.

I'll bet that this is trivial for the gurus however I'm spinning my wheels. Any help greatly appreciated!


Excel 2016 (Windows) 32 bit

	B​	C​	D​	E​	F​	G​	H ​
1​	First Name	Last Name	Birthdate	Hire Date	Termination Date	Gross Amount​	Social Security Number
2​	Employee	One	1/1/1950	10/1/1984		1,000.00​	111-11-1111
3​	Employee	One	1/1/1950	10/1/1984		1,000.00​	111-11-1111
4​	Employee	One	1/1/1950	10/1/1984		1,000.00​	111-11-1111
5​	Employee	One	1/1/1950	10/1/1984		1,000.00​	111-11-1111
6​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
7​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
8​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
9​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
10​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
11​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
12​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
13​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
14​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	1,500.00​	222-22-2222
15​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333
16​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333
17​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333
18​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333
19​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333
20​	Employee	Three	5/15/1970	11/4/1995		1,250.00​	333-33-3333

<tbody>
</tbody>

Sheet: test

<tbody>
</tbody>

You should create a hypothetical outcome so people can see exactly what you want. Visual is always better

 

[jayhill]

Good call. This is the outcome I'm looking for:

Excel 2016 (Windows) 32 bit

	B​	C​	D​	E​	F​	G​	H​
26​	Employee	One	1/1/1950	10/1/1984		4,000.00​	111-11-1111
27​	Employee	Two	2/25/1955	5/28/1985	5/30/2016	13,500.00​	222-22-2222
28​	Employee	Three	5/15/1970	11/4/1995		7,500.00​	333-33-3333

<tbody>
</tbody>

Sheet: test

<tbody>
</tbody>

 

[63falcondude]

Hey Jay, I would copy and paste your data onto a second sheet and then remove duplicates. Delete the values in column G of the second sheet and use a SUMIFS formula to get the correct values in column G.

You can base the formula off of their SS#'s

In F2 of second sheet (test2)

=SUMIFS(test!F:F,test!G:G,test2!G2)

 

[jayhill]

Hey 63falcondude,
That worked like a champ! Thanks for the help!
-Jay

 

[63falcondude]

Glad to help!