CountIf result explanation

Kevin De Hart

I have a series of formula that I am attempting to use to split a compound part number into its part number and revision number and then throw an alert if duplicates are found. Unfortunately my function is returning unpredictable results and I was hoping someone could explain it to me.

You will see in the first image, a column for Part Numbers which is input via forms. This is split into Part and Revision via the following formula.

=LEFT([Part Number]@row, FIND(".", ([Part Number]@row)) - 1)

=RIGHT([Part Number]@row, LEN([Part Number]@row) - FIND(".", [Part Number]@row))

and counted by internal reference into the [Count of Part] column via this formula

=COUNTIF(Part:Part, Part@row)

This causes predictable and functional results about half way down the page however the first part of the page it does not. The only difference I can see is that the part numbers which are functioning all begin with a letter and the rest do not however I have counted numbers with countif before. Am I missing something or not recalling a limitation of the CountIf function?

Jason Tarpinian

I've run into this a few times, and honestly not sure what causes it to happen. But the solution I've found is if you add a helper column, something as simple as

="A" + Part@row

And then do your COUNTIF off of that helper column it should be OK. I imagine it has something to do with SS not being able to recognize whether that split string is a text or number... but it should still at least be able to count itself!

Jason Tarpinian

I've run into this a few times, and honestly not sure what causes it to happen. But the solution I've found is if you add a helper column, something as simple as

="A" + Part@row

And then do your COUNTIF off of that helper column it should be OK. I imagine it has something to do with SS not being able to recognize whether that split string is a text or number... but it should still at least be able to count itself!

Kevin De Hart

It does work, and I should have considered this, it seems a messy way to handle it though.

Thank you for the advice.

Kevin De Hart

As an aside, it does not evaluate as a number when checked with =if(number([Part]@row),1,0)

A good thought on the cause though.

ker9

You can try the formula below to indicate DUP or ok:

=IF(COUNTIF(Part:Part, @cell = Part@row) = 1, "ok", "DUP")

In my test it finds duplicates that are numbers or text but it does not tell you how many duplicates for a particular item. (The first column below contains the "Left" formula from the OP.)

(highlighting is manual to show duplicates)

Hope this helps!