Removing Duplicate Values when using SumIF

[Jongo1]

As you can see in the column sumif I have duplicate values for in row 2 and 3. What I am looking to do is remove that duplicate value so I can get an average of the Sum total for each document number. I need to leave all data intact for a pivot table. Running the pivot table in rows as Item/Document #/Work Center in Columns Type and in values work time, but using the average function averages each individual workcenter rather then the Document as a whole.

I am using the sumif based on =SUMIFS(E:E,B:B,B2,C:C,C2). PS how do you make your tables look pretty on this forum?

A	B	C	D	E	F
Work Center	Type	Item	Document Number	Work Time (Hour)	sumif
YL	Jewel	Forest	1	4.12	4.12
CA	Pol	Forest	1	1.25	1.97
CV	Pol	Forest	1	0.72	1.97
KY	Set	Forest	1	0.83	0.83
NA	Eng	Forest	1	0.9	0.9
CR	Rep	Forest	1	1.25	1.25
DL	Jewel	Sun	2	3.3	17.07
JG	Pol	Sun	2	2	8.43
AT	Set	Sun	2	2.82	5.6
CZ	Jewel	Sun	2	3.75	17.07
AD	Pol	Sun	2	1.38	8.43
CR	Rep	Sun	2	3.33	8.51
CZ	Jewel	Sun	3	3.7	17.07
AD	Pol	Sun	3	2.6	8.43
DL	Jewel	Sun	3	4	17.07
AT	Set	Sun	3	1.8	5.6
CR	Rep	Sun	3	5.18	8.51
CZ	Jewel	Sun	4	2.32	17.07
AD	Pol	Sun	4	2.45	8.43
NA	Eng	Sun	4	0.57	0.57
AT	Set	Sun	4	0.98	5.6

<tbody>
</tbody>

 

[DRSteele]

What version of Excel are you using? If it's Excel 365 Fast Insider, there are new functions just created by Excel that will make quick work of this type of task. Otherwise, the task is more complex.

If you wish to make purdier posts in this forum, you need to download and install add-in (see my signature below for a link). I sue HTML Maker, which makes posts like these:

Book1
	A	B	C	D	E	F
1	Work Center	Type	Item	Document Number	Work Time (Hour)	sumif
2	YL	Jewel	Forest	1	4.12	4.12
3	CA	Pol	Forest	1	1.25	1.97
4	CV	Pol	Forest	1	0.72	1.97
5	KY	Set	Forest	1	0.83	0.83
6	NA	Eng	Forest	1	0.9	0.9
7	CR	Rep	Forest	1	1.25	1.25
8	DL	Jewel	Sun	2	3.3	17.07
9	JG	Pol	Sun	2	2	8.43
10	AT	Set	Sun	2	2.82	5.6
11	CZ	Jewel	Sun	2	3.75	17.07
12	AD	Pol	Sun	2	1.38	8.43
13	CR	Rep	Sun	2	3.33	8.51
14	CZ	Jewel	Sun	3	3.7	17.07
15	AD	Pol	Sun	3	2.6	8.43
16	DL	Jewel	Sun	3	4	17.07
17	AT	Set	Sun	3	1.8	5.6
18	CR	Rep	Sun	3	5.18	8.51
19	CZ	Jewel	Sun	4	2.32	17.07
20	AD	Pol	Sun	4	2.45	8.43
21	NA	Eng	Sun	4	0.57	0.57
22	AT	Set	Sun	4	0.98	5.6
Sheet37

Cell Formulas
Range	Formula
F2	=SUMIFS(E:E,B:B,B2,C:C,C2)

 

[Jongo1]

Thank you, but it's version 2016 and work won't install an update.

What version of Excel are you using? If it's Excel 365 Fast Insider, there are new functions just created by Excel that will make quick work of this type of task. Otherwise, the task is more complex.

If you wish to make purdier posts in this forum, you need to download and install add-in (see my signature below for a link). I sue HTML Maker, which makes posts like these:

	A	B	C	D	E	F
1	Work Center	Type	Item	Document Number	Work Time (Hour)	sumif
2	YL	Jewel	Forest	1	4.12	4.12
3	CA	Pol	Forest	1	1.25	1.97
4	CV	Pol	Forest	1	0.72	1.97
5	KY	Set	Forest	1	0.83	0.83
6	NA	Eng	Forest	1	0.9	0.9
7	CR	Rep	Forest	1	1.25	1.25
8	DL	Jewel	Sun	2	3.3	17.07
9	JG	Pol	Sun	2	2	8.43
10	AT	Set	Sun	2	2.82	5.6
11	CZ	Jewel	Sun	2	3.75	17.07
12	AD	Pol	Sun	2	1.38	8.43
13	CR	Rep	Sun	2	3.33	8.51
14	CZ	Jewel	Sun	3	3.7	17.07
15	AD	Pol	Sun	3	2.6	8.43
16	DL	Jewel	Sun	3	4	17.07
17	AT	Set	Sun	3	1.8	5.6
18	CR	Rep	Sun	3	5.18	8.51
19	CZ	Jewel	Sun	4	2.32	17.07
20	AD	Pol	Sun	4	2.45	8.43
21	NA	Eng	Sun	4	0.57	0.57
22	AT	Set	Sun	4	0.98	5.6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet37​

Worksheet Formulas Cell Formula F2 =SUMIFS(E:E,B:B,B2,C:C,C2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>