COUNT(DISTINCT(COLLECT to count unique values based on multiple criteria

Options

Hi,

I'm trying to count the dates we've worked on a job site, but we have multiple ones. Of course, at first, I'm trying to calculate just for one criterion.

So far, I've tried using the Sheet Summary: =COUNT(DISTINCT(COLLECT(Date:Date, [Site ID]:[Site ID], "CUPERTINO")))

As per my database, I have 80 entries for Cupertino, but counting the days I've worked there for 22 days, and with the formula gives 1 as the value:

Then, I tried counting the days instead of sites:

=COUNT(DISTINCT(COLLECT([Site ID]:[Site ID], Date:Date, "01/01/23")))

How can I count the days I've spent on a job site?

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Denisse A

    Your first formula should return the number of distinct days that equal “CUPERTINO” as the Site ID.

    The DISTINCT function will only work when the data being evaluated are all the same type. When mixed types are present, the function will return the value of 1. Is it possible that you have a mixture of Dates and text within your Date column?

    Try this

    =COUNT(DISTINCT(COLLECT(Date:Date, Date:Date, ISDATE(@cell), [Site ID]:[Site ID], "CUPERTINO")))

    Kelly

  • Denisse A
    Denisse A ✭✭
    Answer ✓
    Options

    Thanks Kelly! We found a way on getting the Site ID instead of adding site by site:

    =COUNT(DISTINCT(COLLECT(Date,Date,[Site ID]@row)))

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Denisse A

    Your first formula should return the number of distinct days that equal “CUPERTINO” as the Site ID.

    The DISTINCT function will only work when the data being evaluated are all the same type. When mixed types are present, the function will return the value of 1. Is it possible that you have a mixture of Dates and text within your Date column?

    Try this

    =COUNT(DISTINCT(COLLECT(Date:Date, Date:Date, ISDATE(@cell), [Site ID]:[Site ID], "CUPERTINO")))

    Kelly

  • Denisse A
    Denisse A ✭✭
    Answer ✓
    Options

    Thanks Kelly! We found a way on getting the Site ID instead of adding site by site:

    =COUNT(DISTINCT(COLLECT(Date,Date,[Site ID]@row)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!