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?



[TABLE="width: 649"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD] E[/TD]
[TD] F[/TD]
[/TR]
[TR]
[TD]Work Center[/TD]
[TD]Type[/TD]
[TD]Item[/TD]
[TD]Document Number[/TD]
[TD] Work Time (Hour)[/TD]
[TD] sumif[/TD]
[/TR]
[TR]
[TD]YL[/TD]
[TD]Jewel[/TD]
[TD]Forest[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4.12[/TD]
[TD="align: right"]4.12[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]Pol[/TD]
[TD]Forest[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]1.97[/TD]
[/TR]
[TR]
[TD]CV[/TD]
[TD]Pol[/TD]
[TD]Forest[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.72[/TD]
[TD="align: right"]1.97[/TD]
[/TR]
[TR]
[TD]KY[/TD]
[TD]Set[/TD]
[TD]Forest[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.83[/TD]
[TD="align: right"]0.83[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD]Eng[/TD]
[TD]Forest[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.9[/TD]
[TD="align: right"]0.9[/TD]
[/TR]
[TR]
[TD]CR[/TD]
[TD]Rep[/TD]
[TD]Forest[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]1.25[/TD]
[/TR]
[TR]
[TD]DL[/TD]
[TD]Jewel[/TD]
[TD]Sun[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3.3[/TD]
[TD="align: right"]17.07[/TD]
[/TR]
[TR]
[TD]JG[/TD]
[TD]Pol[/TD]
[TD]Sun[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8.43[/TD]
[/TR]
[TR]
[TD]AT[/TD]
[TD]Set[/TD]
[TD]Sun[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2.82[/TD]
[TD="align: right"]5.6[/TD]
[/TR]
[TR]
[TD]CZ[/TD]
[TD]Jewel[/TD]
[TD]Sun[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]17.07[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]Pol[/TD]
[TD]Sun[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1.38[/TD]
[TD="align: right"]8.43[/TD]
[/TR]
[TR]
[TD]CR[/TD]
[TD]Rep[/TD]
[TD]Sun[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3.33[/TD]
[TD="align: right"]8.51[/TD]
[/TR]
[TR]
[TD]CZ[/TD]
[TD]Jewel[/TD]
[TD]Sun[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3.7[/TD]
[TD="align: right"]17.07[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]Pol[/TD]
[TD]Sun[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2.6[/TD]
[TD="align: right"]8.43[/TD]
[/TR]
[TR]
[TD]DL[/TD]
[TD]Jewel[/TD]
[TD]Sun[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]17.07[/TD]
[/TR]
[TR]
[TD]AT[/TD]
[TD]Set[/TD]
[TD]Sun[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.8[/TD]
[TD="align: right"]5.6[/TD]
[/TR]
[TR]
[TD]CR[/TD]
[TD]Rep[/TD]
[TD]Sun[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5.18[/TD]
[TD="align: right"]8.51[/TD]
[/TR]
[TR]
[TD]CZ[/TD]
[TD]Jewel[/TD]
[TD]Sun[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2.32[/TD]
[TD="align: right"]17.07[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]Pol[/TD]
[TD]Sun[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2.45[/TD]
[TD="align: right"]8.43[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD]Eng[/TD]
[TD]Sun[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.57[/TD]
[TD="align: right"]0.57[/TD]
[/TR]
[TR]
[TD]AT[/TD]
[TD]Set[/TD]
[TD]Sun[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]5.6[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
YL	Jewel	Forest
CA	Pol	Forest
CV	Pol	Forest
KY	Set	Forest
NA	Eng	Forest
CR	Rep	Forest
DL	Jewel	Sun
JG	Pol	Sun
AT	Set	Sun
CZ	Jewel	Sun
AD	Pol	Sun
CR	Rep	Sun
CZ	Jewel	Sun
AD	Pol	Sun
DL	Jewel	Sun
AT	Set	Sun
CR	Rep	Sun
CZ	Jewel	Sun
AD	Pol	Sun
NA	Eng	Sun
AT	Set	Sun

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Work Center[/TD]
[TD="bgcolor: #FFF2CC"]Type[/TD]
[TD="bgcolor: #FFF2CC"]Item[/TD]
[TD="bgcolor: #FFF2CC"]Document Number[/TD]
[TD="bgcolor: #FFF2CC"]Work Time (Hour)[/TD]
[TD="bgcolor: #FFF2CC"]sumif[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]4.12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]4.12[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1.25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1.97[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0.72[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1.97[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0.83[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]0.83[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0.9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]0.9[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1.25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1.25[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3.3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]17.07[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8.43[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2.82[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5.6[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3.75[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]17.07[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1.38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8.43[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3.33[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8.51[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3.7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]17.07[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2.6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8.43[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]17.07[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1.8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5.6[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]5.18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8.51[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]2.32[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]17.07[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]2.45[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8.43[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]0.57[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]0.57[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]0.98[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5.6[/TD]

</tbody>

Sheet37​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=SUMIFS(E:E,B:B,B2,C:C,C2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]