Home

Problem with Countif function to remove duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-735860%22%20slang%3D%22en-US%22%3EProblem%20with%20Countif%20function%20to%20remove%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735860%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20identify%20duplicates%20based%20on%20a%20unique%20string%20composed%20from%20multiple%20cells%20using%20the%20countif%20function.%20Now%20I%20encounter%20an%20issue%20where%20the%20incorrect%20value%20is%20returned%20for%20values%20that%20are%20clearly%20different%20from%20each%20other.%20In%20the%20attached%20file%20is%20the%20problem%20I%20am%20facing%20visible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20with%20this%20issue%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-735860%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735918%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Countif%20function%20to%20remove%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735918%22%20slang%3D%22en-US%22%3E%3CP%3EHey%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370369%22%20target%3D%22_blank%22%3E%40Ronald_Roos%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20This%3A%3C%2FP%3E%3CP%3E1)%20Insert%20a%20new%20column%20and%20enter%20this%20formula%20to%20extract%20the%20last%20digit%20of%20the%20number%20in%20column%20D%20as%20that%20is%20the%20number%20that%20changes%3A%3C%2FP%3E%3CP%3E%3DRIGHT(D2%2C1)%3C%2FP%3E%3CP%3E2)%20Then%20add%20the%20CountIF%20function%20to%20the%20column%20F.%20Hide%20column%20D%20if%20you%20want%20to%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20315px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122049i3D0C4020DA4C70A0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%221.PNG%22%20title%3D%221.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735901%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Countif%20function%20to%20remove%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370369%22%20target%3D%22_blank%22%3E%40Ronald_Roos%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20this%20formula%20in%20E2%2C%20and%20copy%20down%20rows%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMPRODUCT(--(D%242%3AD2%3DD2))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20your%20original%20formula%20works%20if%20you%20concatenate%20CHAR(173)%20before%20the%20criteria%20argument%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNTIF(D%242%3AD2%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ECHAR(173)%26amp%3BD2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%20origin%20of%20the%20foregoing%20idea%20is%20from%20Lori%20Miller%20in%20this%20link%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fdailydoseofexcel.com%2Farchives%2F2006%2F10%2F10%2Fcountif-bug%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fdailydoseofexcel.com%2Farchives%2F2006%2F10%2F10%2Fcountif-bug%2F%3C%2FA%3E%3C%2FP%3E%3CP%3ESee%20both%20formulas%20in%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-736159%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Countif%20function%20to%20remove%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736159%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%3CBR%20%2F%3EThanks%20for%20the%20help.%20This%20precisely%20was%20what%20I%20was%20looking%20for.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-736242%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Countif%20function%20to%20remove%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736242%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20very%20much%20welcome!%3C%2FLINGO-BODY%3E
Ronald_Roos
New Contributor

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

@Ronald_Roos 

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. 

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 

 

1.PNG

@Twifoo
Thanks for the help. This precisely was what I was looking for.
You’re very much welcome!
Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies