3

I have some product codes, some of which are numbers with leading zeroes, stored in a spreadsheet.

I'm doing some operations that rely on a COUNTIF not equal to function (details in comments but not relevant to the question). Countif seems to break when dealing with strings containing numbers with leading zeroes.

For a simple demonstration

For example if A1 contains "foo" then:

=COUNTIF(A1,"<>"&A1)

returns 0. That is to say, there are no cells in A1 that are not equal to A1 (of course).

But if A1 contains "0001234" as text, then it returns 1, i.e. A1 IS NOT EQUAL TO A1

Further info: =countif(A1:"<>"&"1234") returns 0, so the problem looks to be that excel autmatically converts the string "0001234" to the number 1234, but only in the criteria and not in the range and then finds "0001234"=/=1234.

WORKAROUND: create a helper column with a junk text character appended to all codes. I currently have added a helper column containing ="@"&X1 to all my lists, but it's not an elegant solution.

Improve this question
5
  • shouldn't the formula be something like =Countif(A1:A100,"<>"&A1)?
    – Scott Craner
    Aug 30, 2016 at 16:21
  • Are you trying to identify all products with leading zeroes?
    – Raystafarian
    Aug 30, 2016 at 17:33
  • Hi there ray, always good to see you on a question. The actual function I was using is =COUNTIFS(A:A,A1,B:B,"<>"&B1)<1 Which returns FALSE if there is a record with the same identifier in A but a different value in B. B should be unique to A (but not vice versa) in this data, I'm doing some error checking. (i.e. product 2345 could have attribute 001. There may be other products with atribute 001 too in the list, but 2345 should never have both attribute 001 and 002)
    – Some_Guy
    Aug 31, 2016 at 11:01
  • I didn't include this in the question as I thought it might obfuscate the actual problem I'm facing of countif's behaviour. The task I'm performing may have a more elegant solution than mine, but at the end of the day my formula does do what it should (except with codes with leading 0s). I have a workaround, but it seems worthwhile to have this problem "on the record" as it were.
    – Some_Guy
    Aug 31, 2016 at 11:01
  • Sometimes VBa is a better choice, but I'm assuming that's not an option for you?
    – Dave
    Sep 1, 2016 at 11:42

1 Answer 1

Reset to default
2

EDIT: to change from adding NUL to adding * to the criteria.

You will see similar behavior with Booleans. One way I have found to "force" COUNTIF to not do the conversions is by adding (or pre-pending) a * to the criteria argument. Eg:

=COUNTIF(A1,"<>" & A1 & "*" )

will return 0 even with the string 0001234 in A1

The "*" forces COUNTIF to treat A1 as text in the criteria argument. This will fail, of course, if A1 contains a number. So you might need to test for that

=COUNTIF(A1,"<>" &  A1 & IF(ISNUMBER(A1),"","*"))

And here is a screen shot using the data you posted in your comment:

enter image description here

Since using COUNTIF in this way is not completely bulletproof, you could also consider the EXACT function.

=N(NOT(EXACT(A2,B2)))

or even, more simply, if case sensitivity is not an issue:

=A2<>B2

will return the same results as COUNTIF with the wildcard in the criteria, and also be able exclude the possibility of the range being tested appearing within the criteria.

enter image description here

If I understand the COUNTIFS formula you posted in a comment above, the following might provide the equivalent, but I would like to see data with your expected results.

=SUMPRODUCT((A:A=A2)*(B:B<>B2))<1

Also, the formula would work much more efficiently (quickly), if you could limit the whole column references to being less than a whole column.

Improve this answer
4
  • This seems to just always return 0, even if things aren't equal. See attached image imgur.com/a/6VSYF
    – Some_Guy
    Aug 31, 2016 at 11:16
  • @Some_Guy You are correct, of course. Don't know why I thought it was working. Will Edit to the correct solution.
    – Ron Rosenfeld
    Aug 31, 2016 at 11:27
  • This is better, however it relies on a wildcard being used. With where you can guarantee one item will contain another item, but if I had one code 0002345 and another code 0002345A this would cause a problem. Definitely an improvement on the previous situation though.
    – Some_Guy
    Sep 1, 2016 at 9:51
  • 1
    @Some_Guy Yes, that will be a limitation. Whether it is fatal depends on your database -- only you can judge that. If you are not wedded to using COUNTIF, there are other solutions. I will post one.
    – Ron Rosenfeld
    Sep 1, 2016 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.