COUNTIFS and Duplicate Values

Options

Hello! I need to figure out a way count the number of records in my issue log, EXCEPT if the records have the same parent number, they can only be counted once.

Is there an easy way to do this? Is there a way to use the DISTINCT formula in the "Parent" column, but also count the blank rows, as long as the Record column isn't blank?

I am open to any suggestion! Thank you in advance!

Best Answer

  • kelceyg
    kelceyg ✭✭✭✭✭
    Answer ✓
    Options

    I figured out my own solution:

    Added a helper checkbox row and used a formula to identify duplicates in the "Parent" column: =IF(COUNTIFS(Parent:Parent, Parent@row, Parent:Parent, NOT(ISBLANK(@cell))) > 1, 1)

    I then set up a workflow with the logic "If "Duplicate" column is checked, copy row." I copied the rows to a helper sheet.

    Since my helper sheet only included the rows with duplicates, I was able to utilize the DISTINCT function.

Answers

  • kelceyg
    kelceyg ✭✭✭✭✭
    Answer ✓
    Options

    I figured out my own solution:

    Added a helper checkbox row and used a formula to identify duplicates in the "Parent" column: =IF(COUNTIFS(Parent:Parent, Parent@row, Parent:Parent, NOT(ISBLANK(@cell))) > 1, 1)

    I then set up a workflow with the logic "If "Duplicate" column is checked, copy row." I copied the rows to a helper sheet.

    Since my helper sheet only included the rows with duplicates, I was able to utilize the DISTINCT function.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!