Excluding Duplicate data in a SUMIF

[brandybartz]

My data source has duplicates in it that I need to only sum once and exclude the others. Below is my SUMIF that just sums everything but I need to add a portion that tells it to exclude the duplicates for each section of the formula.

[TABLE="width: 48"]
<colgroup><col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][TABLE="width: 1343"]
<colgroup><col width="1790" style="width: 1343pt; mso-width-source: userset; mso-width-alt: 65462;"> <tbody>[TR]
[TD="width: 1790, bgcolor: transparent"]=IFERROR(SUMIF('Item ST Weekly'!$C$1:$C$20164,CONCATENATE($D$8,"-",$B9,"-","Winner","-","Winner"),'Item ST Weekly'!$M$1:$M$20164)/(SUMIF('Item ST Weekly'!$C$1:$C$20164,CONCATENATE($D$8,"-",$B9,"-","Winner","-","Winner"),'Item ST Weekly'!$M$1:$M$20164)+SUMIF('Item ST Weekly'!$C$1:$C$20164,CONCATENATE($D$8,"-",$B9,"-","Winner","-","Winner"),'Item ST Weekly'!$N$1:$N$20164))," ")[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

If you only need a single record of duplicates, then we can look for the first record.

=IFERROR(VLOOKUP($D$8&"-"&$B9&"-"&"Winner-Winner",'Item ST Weekly'!C1:M20164,11,0)/ (VLOOKUP($D$8&"-"&$B9&"-"&"Winner-Winner",'Item ST Weekly'!C1:M20164,11,0)+VLOOKUP($D$8&"-"&$B9&"-"&"Winner-Winner",'Item ST Weekly'!C1:N20164,12,0))," ")

 

[brandybartz]

I have more than one value that needs to be summed. Is there some kind of qualifier column I could add that would tag the first value as 1 and all other duplicates as 0. Then I could add that as part of my concat for summing??

 

[DanteAmor]

. Is there some kind of qualifier column I could add that would tag the first value as 1 and all other duplicates as 0. Then I could add that as part of my concat for summing??

Hoja5

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]data2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]data3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]data4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]data5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]data6[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]data2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]data3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]data4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]data2[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Formula
[TABLE="class: grid, width: 500"] <tbody>[TR] [TD]Cell	Formula
B2	=IF(COUNTIF(A2:$A$10,A2)>1,0,1)

<tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[brandybartz]

This worked. Thank you

 

[DanteAmor]

Youre welcome