Forum Discussion
COUNTIF cannot distinguish 26 digit number stored as text
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.
