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

learning_grexcel

Active Member
Joined
Jan 29, 2011
Messages
319
I have a column with values like below and their format is text

[TABLE="width: 80"]
<colgroup><col style="width: 80px"></colgroup><tbody>[TR]
[TD="width: 80"]00019[/TD]
[/TR]
[TR]
[TD]00050[/TD]
[/TR]
[TR]
[TD]00080[/TD]
[/TR]
[TR]
[TD]00090[/TD]
[/TR]
[TR]
[TD]000E1[/TD]
[/TR]
[TR]
[TD]000E2[/TD]
[/TR]
[TR]
[TD]000E4[/TD]
[/TR]
[TR]
[TD]000E6[/TD]
[/TR]
[TR]
[TD]000E8[/TD]
[/TR]
</tbody>[/TABLE]



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?
 
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))
 
Upvote 0
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
 
Upvote 0
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&"*"
 
Upvote 0
I have a column with values like below and their format is text

[TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]00019
[/TD]
[/TR]
[TR]
[TD]00050
[/TD]
[/TR]
[TR]
[TD]00080
[/TD]
[/TR]
[TR]
[TD]00090
[/TD]
[/TR]
[TR]
[TD]000E1
[/TD]
[/TR]
[TR]
[TD]000E2
[/TD]
[/TR]
[TR]
[TD]000E4
[/TD]
[/TR]
[TR]
[TD]000E6
[/TD]
[/TR]
[TR]
[TD]000E8
[/TD]
[/TR]
</tbody>[/TABLE]



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&" "))
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top