Forum Discussion
Solved
COUNTIF cannot distinguish 26 digit number stored as text
Hello,
I am trying to make simple list that will count number of repetitions of string in second list (Count how many times equipment with specific serial number was used).
The specific string is a 26-digit string e.g. 28928813202104020000003265. Most often the last 4 digits are different, but this is not a rule.
The CountIF function cannot distinguish the string if only last digits differ.
I’ve tried to modify the sting additionally with TEXT function:
=TEXT(28928813202104020000003267,"00000000000000000000000000")
1)Excel automatically changes the function to: =TEXT(2.8928813202104E+25,"00000000000000000000000000")
2) to force the storage as a string I used:
=TEXT("28928813202104020000003267","00000000000000000000000000")
However, in two cases the result string is “28928813202104000000000000”
I see that the problem is with storing format (double?), but I have no idea how to force excel to see this string as a string.
Solution needs to be applicable over shared excel (multiple users)
Thank you in advance for help!
BR
Piotr
When you enter a number with many digits, Excel interprets only the first 15 of them and replaces the rest with zeros.
So you must enter the values as text, either by formatting the cells as Text before entering any values, or by prefixing the values with an apostrophe '.
Also, COUNTIF tries to treat anything that looks like a number as a number, even if it is formatted as text, so it is not suitable here.
You can use a formula such as
=SUM(--(range=value))
For example:
You can also use the literal value in the formula:
=SUM(--(A2:A13="28928813202104020000003265"))
Remark: if you don't have Microsoft 365 or Office 2021, use SUMPRODUCT instead of SUM.
When you enter a number with many digits, Excel interprets only the first 15 of them and replaces the rest with zeros.
So you must enter the values as text, either by formatting the cells as Text before entering any values, or by prefixing the values with an apostrophe '.
Also, COUNTIF tries to treat anything that looks like a number as a number, even if it is formatted as text, so it is not suitable here.
You can use a formula such as
=SUM(--(range=value))
For example:
You can also use the literal value in the formula:
=SUM(--(A2:A13="28928813202104020000003265"))
Remark: if you don't have Microsoft 365 or Office 2021, use SUMPRODUCT instead of SUM.
- Many thanks Hans, it works perfectly. I was not aware that I can use SUM function in this way.
