Count distinct values, but not all distinct values based on a different set of criteria
Based on Smartsheet limiting the number of columns you may have, I have a situation where unique identifiers from single form entries (inspection IDs), must be carried into another row. This is where the count distinct function comes into play.
=COUNT(DISTINCT(COLLECT({Unique ID}, {Area}, OR(@cell = "Area 1", @cell = "Area 2", @cell = "Area 3", @cell = "Area 4", @cell = "Area 5"), {Date}, AND(IFERROR(MONTH(@cell), 0) = 11, IFERROR(YEAR(@cell), 0) = 2020))))
As you can see the criteria in there that filter the count for the specific areas in one column and the specific date range in another column that I am looking at. This works fine.
However, there are sometimes instances where the same Unique ID is used (within the same criteria specified above) where I would like to count Re-inspections done as well. Inspection (or Unique ID) stays the same, however, whoever fills out the form selects a separate dropdown that says "Re-Inspection 1", "Re-Inspection 2" and so on. Those are unique inspections so I want to count those as well, while at the same time not counting those duplicates that I have eliminated with the formula above.
Any ideas on this much appreciated.
Thank you
Answers
-
Kelly Moore ✭✭✭✭✭✭
Hi Ross
If I understand right, you have inspections and re-Inspections. Each re-inspection of the same ID is a unique event.
A solution could be to join your Unique I’d and Inspection to form a new string and use this string as your unique ID. This would require a helper column - if your sheet has room for that. Not knowing your data I called your drop down column “Inspection category “
In the helper column
= [Unique Id]@row+[Inspection category]@row (Change formula to match your column names)
Then in your index collect use this Joined ID instead of your original {Unique ID}. If I understood your scenario properly, I believe it will give you your distinct counts
-
谢谢你股。我只是试试这个。没有成功,my formulas are yielding a value of 1 now. But I think that we are on the right track.
-
I think that the count distinct collect formula was not working because of a combo of text and numbers in the new joined ID helper column. My own fix just fixed this.
=VALUE([Unique ID]@row + RIGHT([Re-Inspection]@row, 1))
The right-most character in the re-inspection dropdown column is a number 1-10 so it helped to bring those numbers over. Thanks KDM for that help.
-
Now we just need to figure out how to sum values in another column based on this new combined Unique Id column
Not seeing a lot of info out there on SUM unique values.
For instance:
=SUM(DISTINCT(COLLECT({Combined Unique ID}, {Area}, OR(@cell = "Area 1", @cell = "Area 2", @cell = "Area 3", @cell = "Area 4", @cell = "Area 5"), {Date}, AND(IFERROR(MONTH(@cell), 0) = 11, IFERROR(YEAR(@cell), 0) = 2020))))
I seem to SUM together my unique IDs here. I want the criteria to my combined unique ID field be that it is distinct. But I want to SUM another field. Lets call this field the number of work locations inspected that I want to sum.
- Combined unique ID must be distinct
- Area must be specific
- Date range in the specified month and year
Brain not working on a Friday afternoon.
Thanks,
-
Kelly Moore ✭✭✭✭✭✭
Hi Ross
I'm taking a shot at this, not knowing exactly how your data is arranged. I expect we'll have to modify the formula because of this.
I added two helper columns to the source sheet (the cross referenced one) to pull the DISTINCT more easily. Helper#1 is the system generated auto-number column [Row ID]. I reasoned that the first instance of a CombinedID is a Distinct occurrence. I use the minimum Row ID to find these occurrences. Once found, I tag these rows using my second helper column, a checkbox column. I'll call this Checkbox.
If you don't already have the system [Row ID] please add it and save.
Add the checkbox column and in that column, insert this formula
=IF([Row ID]@row = MIN(COLLECT([Row ID]:[Row ID], CombinedID:CombinedID, CombinedID@row)), 1)
I will assume that your column [number of work locations inspected] actually contains numbers. Although we can count anything, we can only sum numbers.
On your target sheet, place this formula in a Text/Number column
=SUMIFS({number of work locations inspected}, {Test Sheet Checkbox}, @cell = 1, {Test Sheet Area}, OR(@cell = "Area 1", @cell = "Area 2", @cell = "Area 3", @cell = "Area 4"), {Date}, AND(IFERROR(MONTH(@cell), 0) = 11, IFERROR(YEAR(@cell), 0) = 2020))
Give this a try, modifying the Area to meet your specific needs. I used the same criteria you had used in your earlier request.
Kelly
-
Worked like a charm. Thank you Smartsheet Wizard. Up top
-
Kelly Moore ✭✭✭✭✭✭
So glad it worked! Happy to help
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-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"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-26T01:04:51+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":23,"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":"