Countif not giving correct number

[anilkumar.n]

Hi,

I have column T with the below mentioned values

T5 224362161121934567688261099708
T6 224362161121934568290661099708
T7 224362061155685267688261099753
T8 44218081233978867376660032621
T9 44218081233978867666360032621

my formula is like this

=IF(COUNTIF(T:T,T5)>1,"Duplicate","")

here eventhough T5 and T6 are not same, I am getting a value Duplicate...

Please help

[coinbank]

That should fix it

=IF(COUNTIF(T:T,">1"&T5),"Duplicate","")

[clownfish]

I tried this and it doesn't resolve to "duplicate" for T5. You might want to check for some simple mistake.

If nothing else, do some error checking by replacing your formula with this:

=IF(COUNTIF(T:T,T5)>1,"cells "&ADDRESS(ROW(),COLUMN(T$1),4)& " and "&ADDRESS(MATCH(T5,T6:T$10000,0)+ROW(),COLUMN(T$1),4)&" are the same","")

See what that tells you; if it finds a match of a cell somewhere else in column T, it will give you a message like "cells T5 and T10 are the same". Then check to see whether this is actually true....

EDIT:

Nevermind-- the problem you're having is that Excel can only handle 15 digit precision, so it is in fact evaluating T5 and T6 as the same value. You would need to convert your numbers to text strings in order for COUNTIF to distinguish one from the other, I believe. Frustrating!

Do this instead:

=IF(ISNA(MATCH(T5,T6:T$10000,0)),"","duplicate at cell "&ADDRESS(MATCH(T5,T6:T$10000,0)+ROW(),COLUMN(T$1),4))

[Ron Coderre]

COUNTIF seems to convert numeric text to numbers. Consequently, if there
are more than 15 significant digits, Excel rounds off the excess.

Try something like this:

=IF(SUMPRODUCT(--($T$2:$T$30=T5))>1,"Duplicate","")

Is that something you can work with?

[anilkumar.n]

Hi Ron,

I'm getting #NUM! error when I Entered this formula

=IF(SUMPRODUCT(--(T:T=T5))>1,"Duplicate","")

Regards,

Anil

[arthurbr]

You cannot use ranges like T:T with sumproduct
Use something like T1:T65535