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
-
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
-
Thank you KDM. I did just try this. No success, as 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,
-
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 🙏
-
So glad it worked! Happy to help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!