Count Formula

jgneely72151jgneely72151 ✭✭✭✭✭
edited 05/21/20 in Formulas and Functions
05/21/20 Edited 05/21/20
Accepted

Hello!

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.

Popular Tags:

Best Answers

  • jgneely72151jgneely72151 ✭✭✭✭✭
    Accepted 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!

Answers

  • jgneely72151jgneely72151 ✭✭✭✭✭

    Thanks Paul! I figured it out last night. I now see where I erred. I didn't give you all of the details. My primary column includes the concatenation of the first name, last name, and site. In my rollup sheet, I was trying to use that column for the base of the formula and realized that it was not going to work due to the fact that while there were multiple names (the dupes) but the sites were also included for each name, thus making them all unique. So, I used the email address column and it returned the value I was looking for when the dupes were removed. I think I will need to fix this later but for now, I needed the quick results.

    For future reference, I'll think about this when laying the foundation of my sheet. The formula you provided will definitely be put to use.

    Again, thank you so much and so sorry to trouble you with this.

    Have an awesome day!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help and no worries! 👍️

  • jgneely72151jgneely72151 ✭✭✭✭✭

    Perfect! Thank you so much!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

  • jgneely72151jgneely72151 ✭✭✭✭✭

    Paul i spoke too fast. So for that particular example it returned 1 but I have another situation where it should be 3 and I'm not getting that return.

  • jgneely72151jgneely72151 ✭✭✭✭✭

    Here is an example as I did not give enough scenarios.

    I need to make sure I don't count the dupes from the emails but I need to count the checks in each of those 4 columns. The numbers will vary. I used the formula and was using it for each category but I keep getting 1 for my answer when I know that is not true. Hope this makes some sense.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I'm not sure I follow. Based on your above screenshot the counts appear correct.

  • jgneely72151jgneely72151 ✭✭✭✭✭

    So sorry...I drew that up in Excel as an example. When I used the formula in SmartSheets, for some reason it worked on the CRS column but is not working for the other fields. I'm getting a count of "1" for all of the other columns when I should be getting a count of "2" or "3" for the respective columns. Let me just go back to make sure I don't have any typos or misplaced commas.

  • jgneely72151jgneely72151 ✭✭✭✭✭

    Thank you so much! I misplaced my comma. I appreciate the patience and guidance. Have an awesome day!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

Sign In or Register to comment.