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

Options

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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Options

    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
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    edited 12/12/20
    Options

    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
    Options

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    So glad it worked! Happy to help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!