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 Reinspections done as well. Inspection (or Unique ID) stays the same, however, whoever fills out the form selects a separate dropdown that says "ReInspection 1", "ReInspection 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 reInspections. Each reinspection 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([ReInspection]@row, 1))
The rightmost character in the reinspection dropdown column is a number 110 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 autonumber 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
Check out the Formula Handbook template!