Count Formula
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.
Best Answers
-
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)))
Answers
-
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 ✭✭✭✭✭✭
-
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 ✭✭✭✭✭✭
-
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 ✭✭✭✭✭
抱歉……我画在Excel中,作为一个例子。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 ✭✭✭✭✭✭
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"