Count Formula

jgneely72151

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.

Paul Newcome

The easiest way to do this would be to create a helper column that joins the first and last name columns together. Then you can use something along the lines of the below to get a count of how many different names you have while excluding the blank rows:

=COUNT(DISTINCT(COLLECT([Helper Column]:[Helper Column], Helper Column]:[Helper Column], @cell <> "")))

jgneely72151

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!

Paul Newcome

You would add that in the COLLECT function.

=COUNT(DISTINCT(COLLECT(Email:Email, Email:Email, @cell <> "", CRS:CRS, 1)))

Paul Newcome

I tested, and it worked out fine for me. The only thing you should need to change is the second range.

=COUNT(DISTINCT(COLLECT(Email:Email, Email:Email, @cell <> "", CRS:CRS, 1)))

=COUNT(DISTINCT(COLLECT(Email:Email, Email:Email, @cell <> "", ALC:ALC, 1)))

=COUNT(DISTINCT(COLLECT(Email:Email, Email:Email, @cell <> "", VLS:VLS, 1)))

=COUNT(DISTINCT(COLLECT(Email:Email, Email:Email, @cell <> "", RSU:RSU, 1)))

Paul Newcome

The easiest way to do this would be to create a helper column that joins the first and last name columns together. Then you can use something along the lines of the below to get a count of how many different names you have while excluding the blank rows:

=COUNT(DISTINCT(COLLECT([Helper Column]:[Helper Column], Helper Column]:[Helper Column], @cell <> "")))

jgneely72151

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 Newcome

Happy to help and no worries! 👍️

jgneely72151

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!

Paul Newcome

You would add that in the COLLECT function.

=COUNT(DISTINCT(COLLECT(Email:Email, Email:Email, @cell <> "", CRS:CRS, 1)))

jgneely72151

Perfect! Thank you so much!

Paul Newcome

Happy to help! 👍️

jgneely72151

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.

jgneely72151

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 Newcome

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

jgneely72151

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.

Paul Newcome

I tested, and it worked out fine for me. The only thing you should need to change is the second range.

=COUNT(DISTINCT(COLLECT(Email:Email, Email:Email, @cell <> "", CRS:CRS, 1)))

=COUNT(DISTINCT(COLLECT(Email:Email, Email:Email, @cell <> "", ALC:ALC, 1)))

=COUNT(DISTINCT(COLLECT(Email:Email, Email:Email, @cell <> "", VLS:VLS, 1)))

=COUNT(DISTINCT(COLLECT(Email:Email, Email:Email, @cell <> "", RSU:RSU, 1)))

jgneely72151

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

Paul Newcome

Happy to help! 👍️