Duplicates from Excel

Nicole Hodges

Hey Guys!

I am wanting to count duplicates from a dump in Excel for one column (Supplier Id). I know my formula is correct but it seems to not be counting correctly because either 1) the tick mark added to the beginning of the number from Excel or 2) the amount of zeros? Any ideas to fix this?

Formula: =COUNTIFS([Supplier Id]:[Supplier Id], [Supplier Id]@row)

Example of Supplier ID: '000001777 (all have many zeros before some #s)

David Tutwiler

You might be able to get around it using CONTAINS. The only thing to worry about might be duplicates. It would look like:

=COUNTIF([Supplier Id]:[Supplier Id], CONTAINS([Supplier Id]@row, @cell) > 0)

Nicole Hodges

Thanks David! I am getting "INVALID OPERATION" hmm...

David Tutwiler

Try this, sorry:

=COUNTIF([Supplier Id]:[Supplier Id], CONTAINS([Supplier Id]@row, @cell))

Nicole Hodges

That did it! Thank you so much!!

Nicole Hodges

What if I wanted to include a criteria? Would want to look at only Dups with US as Region and only Dups with EU as region. Any idea where/how I would add that one in?

David Tutwiler

Sure thing. You'd add it as your second criteria in a COUNTIFS. Something like:

=COUNTIFS([Supplier Id]:[Supplier Id], CONTAINS([Supplier Id]@row, @cell), Region:Region, "US")