Formula - Evaluate for Double Count IF condition is met

[SteveOranjin]

Hello,

I am in the middle of writing a formula that evaluates whether or not a column has any duplicates in it. The problem is, is that I don't know how to make the formula evaluate duplicates where that condition is met.

If the header is "SKU" then I would like the formula to count the number of duplicates and return the number. So,

IF column C has header "SKU"
Then
Count numebr of duplicates.

If Column does NOT have "SKU" in title, than do not count duplicates.

Steve

 

[SteveOranjin]

This is what I have. I think it is pretty close.

COUNTIF(SUBSTITUTE(ADDRESS(1,IF(A1="SKU","1",IF(B1="SKU","2",IF(C1="SKU","3",IF(D1="SKU","4",IF(E1="SKU","5",IF(F1="SKU","6"))))))),1,""):SUBSTITUTE(ADDRESS(1,IF(A1="SKU","1",IF(B1="SKU","2",IF(C1="SKU","3",IF(D1="SKU","4",IF(E1="SKU","5",IF(F1="SKU","6"))))))),1,""),SUBSTITUTE(ADDRESS(1,IF(A1="SKU","1",IF(B1="SKU","2",IF(C1="SKU","3",IF(D1="SKU","4",IF(E1="SKU","5",IF(F1="SKU","6"))))))),1,""))

 

[Eric W]

I'm not entirely sure what you're asking, but maybe:

	A	B	C	D	E	F	G	H	I
something	else	SKU	something	else	done	Number of duplicates in the SKU column

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

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

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

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

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

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

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

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

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

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

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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] "]I2[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IFERROR(MATCH(OFFSET(A2:A100,0,MATCH("SKU",A1:F1,0)-1),OFFSET(A2:A100,0,MATCH("SKU",A1:F1,0)-1),0),1),ROW(A2:A100)-ROW(A2)+1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[SteveOranjin]

HI Eric,

It seems to be returning one less than the actual amount.

=IF(SUM(SIGN(FREQUENCY(IFERROR(MATCH(OFFSET('Data Sheet'!A1:A100,0,MATCH("SKU",'Data Sheet'!A1:F1,0)-1),OFFSET('Data Sheet'!A1:A100,0,MATCH("SKU",'Data Sheet'!A1:F1,0)-1),0),1),ROW('Data Sheet'!A2:A100)-ROW(A2)+1)))=1,"No Duplicates","Duplicates")

 

[Eric W]

Can you show me your sample data?

 

[SteveOranjin]

Can you show me your sample data?

https://1drv.ms/x/s!AuLIQSvmFBoEiHrGlGsYghNOGtOw

 

[SteveOranjin]

Anyone wanna help a brother out here?

 

[Eric W]

I looked at your spreadsheet, but wasn't able to copy the data to test with. I see some possibilities though. Try this:

=IF(SUM(SIGN(FREQUENCY(IFERROR(IF(OFFSET('Data Sheet'!A2:A200,0,MATCH("SKU",'Data Sheet'!A1:F1,0)-1)<>"",MATCH(OFFSET('Data Sheet'!A2:A200,0,MATCH("SKU",'Data Sheet'!A1:F1,0)-1),OFFSET('Data Sheet'!A2:A200,0,MATCH("SKU",'Data Sheet'!A1:F1,0)-1),0),""),1),ROW('Data Sheet'!A2:A200)-ROW(A2)+1)))=1,"No Duplicates","Duplicates")

I changed the upper limit to 200, and the lower limit to 2. I also added a check to make sure that empty cells aren't counted. Hope this works better.

 

[SteveOranjin]

I looked at your spreadsheet, but wasn't able to copy the data to test with. I see some possibilities though. Try this:

=IF(SUM(SIGN(FREQUENCY(IFERROR(IF(OFFSET('Data Sheet'!A2:A200,0,MATCH("SKU",'Data Sheet'!A1:F1,0)-1)<>"",MATCH(OFFSET('Data Sheet'!A2:A200,0,MATCH("SKU",'Data Sheet'!A1:F1,0)-1),OFFSET('Data Sheet'!A2:A200,0,MATCH("SKU",'Data Sheet'!A1:F1,0)-1),0),""),1),ROW('Data Sheet'!A2:A200)-ROW(A2)+1)))=1,"No Duplicates","Duplicates")

I changed the upper limit to 200, and the lower limit to 2. I also added a check to make sure that empty cells aren't counted. Hope this works better.

Why are you adding limits? I'm hoping to be able to find the column with the header "SKU" and then count all of the duplicates in the column. Is it not possible to have it count for the entire column?

Steve

 

[Eric W]

Because whole column references in a formula like this can really slow down your worksheet. Excel has over a million rows, and a formula like this has to check every single one, which is really inefficient if most of them are empty. The rule of thumb is to pick the highest row you think you'll ever hit, then add 10%. Even if you pick 100,000, that's still 10 times better than a whole column reference. The other option would be to create a Name that calculates the last used row, then include that in your OFFSET functions. If you want to try that, let me know if your SKUs are numeric or alphanumeric.