Countif does not work with numbers stored as text

[illusionek]

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?

Please Login or Register  to view this content.

[mcmahobt]

What value is the countif returning? Is it an error?

[TMS]

Maybe try:

Please Login or Register  to view this content.

Regards, TMS

[illusionek]

Please see below example of data, following numbers are stored as text if you store them as number the last thee digits are changed to 000. This is the reason they are stored as text.

However when I do Countif, formula sees them as numbers insetad of text that is converts last three digits to 0, so would count them as the same value.


Using --A1 did not help. I would be grateful for some further suggestions.


250603365001543285
250603365001543032
250603365001543704

[romperstomper]

You'll have to use SUMPRODUCT:

=SUMPRODUCT(($A$2:$A$46=A2)+0)