finding repetition of values using countif doesn't give correct result

[learning_grexcel]

I have a column with values like below and their format is text

00019
00050
00080
00090
000E1
000E2
000E4
000E6
000E8

<colgroup><col style="width: 80px"></colgroup><tbody>
</tbody>

When I write a formula as below to find if there is any repetition in the column, it shows result like 2, 4, 100, 25, etc etc. particularly for the values I mentioned above but in real there is no repetition. The result should have been "1".

= countif(A:A,A1)


Is my formula incorrect or the issue is something else?

 

[norulen]

Text format is causing some issues. the formula you are using is correct though. I dont know what the best way of doing this but a quick fix would be this
=COUNTIF($A$1:$A$9,"*"&RIGHT(A1,5))

 

[learning_grexcel]

wow! Thanks norulen. It works indeed.

Could you please explain to me what the issue was and how your formula works to fix it?

 

[norulen]

I myself doesn know what exactly was the issue. But it has something to do with the text format. So what i have done in my formula is to do countif using only last 5 characters of the text instead of searching the entire text

 

[learning_grexcel]

Thank you. But I think it worked because of the "*" in the front. Is it changing the format to number here?

 

[norulen]

No it does. "*" is basically to search for a text where the first few characters could be different. for example if the word we want to count is ABC but the data is in slightly different form like XXABC you we can use "*" & ABC in that case. SImiliarly if it was XXABCXX we can use "*"&ABC&"*"

 

[Aladin Akyurek]

I have a column with values like below and their format is text

00019
00050
00080
00090
000E1
000E2
000E4
000E6
000E8

<tbody>
</tbody>

When I write a formula as below to find if there is any repetition in the column, it shows result like 2, 4, 100, 25, etc etc. particularly for the values I mentioned above but in real there is no repetition. The result should have been "1".

= countif(A:A,A1)


Is my formula incorrect or the issue is something else?

Another option is:

B1, control+shift+enter, not just enter, and copy down:

=COUNT(FIND(" "&$A$1:$A$9&" "," "&A1&" "))