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?

Find more posts tagged with

Accepted answers

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!

All comments

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

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.