CountIf result explanation
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?
Best Answer

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  Sevan Technology
Smartsheet Aligned Partner
Answers

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  Sevan Technology
Smartsheet Aligned Partner

It does work, and I should have considered this, it seems a messy way to handle it though.
Thank you for the advice.

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.

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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!