Count distinct values, but not all distinct values based on a different set of criteria

Ross Crawford

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

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

Ross Crawford

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.

Ross Crawford

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.

Ross Crawford

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

Ross Crawford

Worked like a charm. Thank you Smartsheet Wizard. Up top 🙏

Kelly Moore

So glad it worked! Happy to help