Identify duplicates in a column excluding blanks and a second criteria

bketch ✭✭
edited 05/11/23 in Formulas and Functions

I am trying to identify duplicates excluding blanks and another criteria in the same column.

Exp. The column has phone numbers, blanks, and a word like Not Available

This is as far as I have gotten but cannot find a way to exclude the "Not Available" or make it 0 as well.

I have identified dupes and blanks so far...

=IF([Phone]@row = "", 0, “COVP”, 0,IF(COUNTIFS([Phone]:[Phone], [Phone]@row) = 1, 0, 1))

I appreciate any help!


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 05/11/23

    Hey @bketch

    If I understand your request, you only want the formula to run when the row contains a number, and not when the row contains words or blanks? If I understood correctly, then try this.

    =IFERROR(IF(ISNUMBER(VALUE(LEFT(Phone@row, 3))), 1), 0)

    Will this work for you?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!