• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif returns wrong result

P

piseth834

New Member
Hi,


I am new to this site. I need some advice on how to handle this countif:

Column A

741006771715375824965518848564848508971679087

741006771715375485099518848564848508971678187

741006771717675824965518848564848508971679087

741006771717675485099518848564848508971678187

741006771719875824965518848564848508971679087

741006771719875485099518848564848508971678187

741006771725375824965518848564848508971679087

741006771725375485099518848564848508971678187

741006771727675824965518848564848508971679087

741006771727675485099518848564848508971678187

741006771729875824965518848564848508971679087

741006771729875485099518848564848508971678187

741006771735375824965518848564848508971679087

741006771735375485099518848564848508971678187

741006771737675824965518848564848508971679087

741006771737675485099518848564848508971678187

741006771739875824965518848564848508971679087

741006771739875485099518848564848508971678187

741006771745375824965518848564848508971679087

741006771745375485099518848564848508971678187

741006771747675824965518848564848508971679087


If I do countif to this column, it will return value 2 for each row even though each row is different/unique. Could you help explain this and how to fix it.


Thanks
 
I'm not sure...if you are trying to identify unique values, you can use this formula:


=SUMPRODUCT((A1:A21<>"")/COUNTIF(A1:A21,A1:A21&""))


But that returns 11. There are 21 unique values, so this must only count out to a certain digit. Maybe the experts know?
 
I took a quick look, and I would have to guess that since the numbers are so big ( when I copied and pasted into my excel it gave me x.xx+Exx) so using the countifs it probably only goes out so many numbers. Probably need to break that number up into some a little more manageable
 
Hi ,


Nick M is right ; the data cannot be treated as numbers. Try this instead :


=SUMPRODUCT(--(Data_Range=Cell_Value))


The above will return 1 if there are no duplicates.


Data_Range can be A2:A2000 or whatever is your data range.


Cell_Value is A2


As you copy this downwards , Cell_Value will change to A3 , A4 , A5 ,...


This assumes that your data is present in column A the way you have posted it here ; if it is being displayed as numbers in scientific format , e.g. 7.41006771715375E+44 , then even the above formula will not work , unless the difference is in the first 15 digits.


Narayan
 
Your numbers exceed Excel's criterion:

See here:

http://support.microsoft.com/kb/269370


So you need to format the cells as 'Text' to handle such numbers.
 
Hi Shrivallabha ,


I think if you try it out , you will find the issue is not one of cell formatting alone ; I formatted three cells as text , entered the following text strings :

[pre]
Code: 
741006771715375824965518848564848508971679087
741006771715375485099518848564848508971678187
741006771715375824965518848564848508971679088
[/pre]
and used the following formula in the first cell , and dragged it down for the remaining 2 cells :


=COUNTIF($C$2:$C$4,C2)


The above formula returned 3 in all 3 cases , since the first 15 characters in all 3 text strings were the same.


The problem is with the COUNTIF function.


Narayan
 
Hi Narayan,


Oops! The post came out in confusing manner and I should have stated clearly. I was answering to what Nick M had stated wrt OP's data and not to what OP had posted. Sorry for the confusion.


COUNTIF gives me the results same as yours. And I guess it handles numbers in the manner stated in KB as you've validated.
 
You must log in or register to reply here.
Back
Top