How to get distinct values from "Host Manager Name" and "Active Contractors"?

I am trying to consolidate the "Host Manager Name" column to state the name once in the "Distinct Name" column. I would like the same for the "Active Contractors" column, to state the number once in the Max Number Column.

I followed the instructions from the link below:

It worked for the first row, but as I dragged the formula down, I got a "No Match" and "Invalid Operation". I also could not convert the formula in the "Count" Column into a column formula because the system said there was an issue with the syntax.

The formula in the "Count" Column is:

=IF(COUNTIFS([Host Manager Name]$2:[Host Manager Name]@row, [Host Manager Name]@row) = 1, COUNT(DISTINCT([Host Manager Name]@row)))

The formula in the "Distinct Name" Column is:

=INDEX([Host Manager Name]:[Host Manager Name], MATCH(Number@row, Count:Count, 0))

The formula in the "Max Number" column is:

=MAX(COLLECT(Number:Number, [Host Manager Name]:[Host Manager Name], [Distinct Name]@row))


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    My suggestion would be to insert an auto-number column with no special formatting (called "Auto") and a text number column (called "Row" with the following column formula:

    =MATCH(Auto@row, Auto:Auto, 0)

    Then to get the first entry of each name...

    =IF(COUNTIFS(Row:Row, @cell <= Row@row, [Host Manager Name]:[Host Manager Name], @cell = [Host Manager Name]@row) = 1, [Host Manager Name]@row)

    Then to pull the number in:

    =IF([Distinct Name]@row <> "", MAX(COLLECT(Number:Number, [Host Manager Name]:[Host Manager Name], @cell = [Distinct Name]@row))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!