I am trying to use below simple Countif but it does not work, I am sure it has something to do with the fact that value in A2 is number stored as text.

I tried to wrap A2 in Text(A2,0) but did not help. I am sure values in cells are correct because when I compare i.e. A2 with A3 using If formula I get a match but when I do Countif I get meaningless numbers.

Also when I convert text to numbers my countif works but unfortunately I need to keep the formatting.

Any help please?

=COUNTIF($A$2:$A$46,A2)