Count Formula

jgneely72151 ✭✭✭✭✭
edited 05/21/20 in Formulas and Functions


I am tracking assignments and a person can have more than one assignment. I need to be able to count the number of persons assigned on the sheet but don't want to count dupes. The fields are "First Name" and "Last Name". I'm trying to use the =COUNT(DISTINCT(RANGE) formula but I'm not having any success. Also, there are blank rows that lists the assignments by individual. Not sure if it's counting the blanks. Any assistance is greatly appreciated.

Best Answers

  • jgneely72151
    jgneely72151 ✭✭✭✭✭
    Answer ✓

    Hi Again!

    Need your help. Below is an example of the fields i'm working with.

    Since I did not concatenate my names, I used the email field as the row to remove dupes to get an accurate headcount. Worked perfectly; however, I now need to provide an accurate count for those that have check marks under CRS and i realized that I was counting the dupes. So, I tried using the =count(distinct(collect) function but not sure how to write the formula. The first part of the formula that counts the unique email addresses worked and returned 2 but i'm getting an error on the CRS portion because I need to report that there is only 1 person that's actually checked for that field.

    Any help you can provide is greatly appreciated!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!