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

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    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

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    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

  • 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.

  • ker9
    ker9 ✭✭✭✭✭✭
    edited 05/05/23

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!