Countif(s) evaluating text as number

[NifVT]

I've searched on this and cannot find anything relevant. The basic issue is that I have a column that is formatted as text and contains text. For ease of understanding this simple example explains by issue. My actual data set it much larger.

ColumnA
02878
2878

=Countif(A1:A2,A1) Returns 2. The formula A1=A2 returns false. The fomula Type(A1) and Type(A2) shows them both as 2 (text). It is evalutating both as the same number vs two different text entries however when I use either countif or countifs.

In my master spreadsheet I have parsed the data I am counting out of a master text string. The full text string may be different, but the parsed data identifies things that are of the same class and I want to count occurances of items of the same class. 2878 and 02878 are really different..... I actually have 4 of one and 1 of the other but I get a total count of 5 everytime.

Has anyone else ever run into this and what's the best way around it? I doubt I have too many more like this, but I randomly chanced upon this when I was validating.

 

[NeonRedSharpie]

So, I have tried everything I can think of. The only thing I would consider is a helper column to check the length of the string. This will return 5 and 4 for your sample data. Then you can do a countifs() on the len as well?

 

[Weazel]

maybe...

=SUMPRODUCT(--(A1:A2=A1))

 

[platonic567]

I guess you would have to use another function to count.

Try below, it returns 1 for A1 and A2:

=SUMPRODUCT(--($A$1:$A$2=A1))

Edit: That's awkward, just discovered that Weazel and I had the same idea after I posted.

 

[NifVT]

Thanks all. I will probably go with sumproduct, which seems the most straight forward. I had contemplated added an additional Text string to the beginning of each parsed item in a seperate column as well. That would behave similar to the Len idea, in that it would have added an additional dimension to make it unique. But it adds more data and columns too. Ah the trade-off...

 

[NifVT]

Works perfectly even using table name references