Count and Occurrence of Duplicates in a Column

I'm trying to count the number of duplicates in a column and then number those duplicates in another column.

For example:

I'm using the formula COUNTIF([Legacy ID]:[Legacy ID], [Legacy ID]@row) in the Multiples column to count the number of duplicates in the Legacy ID column. I need help with a formula to create the MCount column.

Thanks for your help!

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    To set this up so that we can use strictly column formulas, we will need to insert two more columns. The first is an auto-number column that doesn't require any special formatting. The second is a text/number column with the following column formula:

    =MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)

    I'll call this text/number column "Row Number" for the sake of this example since it will duplicate the row numbers into something we can leverage, but you can call them whatever you want.


    Now we can move onto putting together a formula that will allow you to remove the Multiple column all together which will save a little space since we added two columns.


    So we already have the second number in the "# of #" string with your COUNTIFS:

    =COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row)


    To get the first number, we will leverage that [Row Number] column to count any rows that have a row number less than or equal to the current row number.

    =COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row)


    Now we piece it together like this...

    =first_#_formula + " of " + second_#_formula


    =COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) + " of " + COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this...

    =IF([Legacy ID]@row <> "", COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row, [Row Number]:[Row Number], @cell <= [Row Number]@row) + " of " + COUNTIFS([Legacy ID]:[Legacy ID], [Legacy ID]@row))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!