Counting Distinct Contacts in a Contact List Column

Hello. I have a tracker and in it I am using a Contact List for "Assigned To" and I allow for that to contain multiple contacts.

I am trying to count the number of distinct individuals in the column to come up with a total number of contributors to the effort we're tracking.

I used: =COUNT(DISTINCT(Leads:Leads))

The result was 10, except I know it is actually 21.

Any ideas? Thank you in advance!


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    One possible way to do this is with a metrics sheet. Create a "Leads" column containing your all members of contact list. In a second column "Effort," reference your first sheet to count the number of times each lead appears in that column:

    =COUNTIFS({Main Sheet Assigned To}, CONTAINS(Leads@row, @cell))

    Then, wherever you're trying to count the number of distinct Leads, use:

    =COUNTIF({Metrics Sheet Leads}, >0)


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!