Countif(s) evaluating text as number

NifVT

New Member
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.

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

NeonRedSharpie

Well-known Member
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

Well-known Member
maybe...

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

platonic567

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

Try below, it returns 1 for A1 and A2:

Code:
``=SUMPRODUCT(--(\$A\$1:\$A\$2=A1))``

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

NifVT

New Member
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

New Member
Works perfectly even using table name references

Replies
4
Views
1K
Replies
3
Views
538
Replies
8
Views
3K
Replies
1
Views
371
Replies
1
Views
260

1,190,631
Messages
5,982,029
Members
439,750
Latest member
megaman777

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.

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

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