• 460K Members
• 11.1K Online
• 558K Conversations

Highlighted
New Contributor

# Problem with Countif function to remove duplicates

Dear all,

I am trying to identify duplicates based on a unique string composed from multiple cells using the countif function. Now I encounter an issue where the incorrect value is returned for values that are clearly different from each other. In the attached file is the problem I am facing visible.

Can anyone help me with this issue?

4 Replies

# Re: Problem with Countif function to remove duplicates

Use this formula in E2, and copy down rows:

=SUMPRODUCT(--(D\$2:D2=D2))

By the way, your original formula works if you concatenate CHAR(173) before the criteria argument like this:

=COUNTIF(D\$2:D2,
CHAR(173)&D2)

The origin of the foregoing idea is from Lori Miller in this link:

http://dailydoseofexcel.com/archives/2006/10/10/countif-bug/

See both formulas in the attached file.

# Re: Problem with Countif function to remove duplicates

Hey @Ronald_Roos

Try This:

1) Insert a new column and enter this formula to extract the last digit of the number in column D as that is the number that changes:

=RIGHT(D2,1)

2) Then add the CountIF function to the column F. Hide column D if you want to

# Re: Problem with Countif function to remove duplicates

@Twifoo
Thanks for the help. This precisely was what I was looking for.

# Re: Problem with Countif function to remove duplicates

You’re very much welcome!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies