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!