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
 Smartsheet Customer Resources
 62.1K Get Help
 352 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!