SOLVED

COUNTIF cannot distinguish 26 digit number stored as text

Copper Contributor

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

2 Replies
best response confirmed by PiotrL1750 (Copper Contributor)
Solution

@PiotrL1750 

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:

HansVogelaar_0-1693384619682.png

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.
1 best response

Accepted Solutions
best response confirmed by PiotrL1750 (Copper Contributor)
Solution

@PiotrL1750 

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:

HansVogelaar_0-1693384619682.png

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.

View solution in original post