COUNTIF Seems Not To Work With Text Containing Leading Zeros?

Question

Hello,

I deal with a lot of batch/lot numbers that are automatically generated and start with leading zeros (e.g. 000012345). I'm trying to create a column simply to count the instances of a lot number within the column in order to quickly identify duplicates. The formula I have is

=COUNTIF([Lot Number]:[Lot Number],[Lot Number]@Row)

But every single result shows as "0" - which to me means it's not counting any of them, including the one @Row.

I've done a couple tests to prove that the cell is Text (and confirmed not any other data type).

Thoughts or suggestions?

Paul Newcome · Accepted Answer

Try a helper column using a VALUE function to remove the leading zeros and ensure consistent data types (all numbers instead of a mix of numbers and text strings), then check for duplicates there.

cwaring · Accepted Answer

Interesting approach - I tested and this works as well to just convert all the text to numbers.

I actually randomly tried a different approach with a helper column to prepend a letter to the lot number:

="x" + [Lot Number]@Row

This seemed to fix the issue as well.