Count Distinct Collect Returning 1

I am trying to populate a metrics sheet from data contained is a source sheet. In this scenario I am trying to get distinct values that fall between two dates.

This is the formula that I am trying to get to work, but it is currently returning 1.

=COUNT(DISTINCT(COLLECT({PAS Assigned}, {Date Logged} >= $[Start Date]@row, {Date Logged} <= $[End Date]@row)))

As a test I tried to simplify using something other than dates.

=COUNT(DISTINCT(COLLECT({PAS Assigned}, {Rating} >= 1)))

In each case the formula is returning 1, when this is not the case. If I create a count(distinct(collect())) formula in the source sheet it works correctly.

Am I doing something wrong?

Thank you,

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @earl_bennett

    Paul is meaning commas between the {range} and the criteria >=$[Start Date]@row

    So in your example above:

    =COUNT(DISTINCT(COLLECT({Week Number}, {Rating} = 1)))

    There's no comma after {Rating}

    Try this:

    =COUNT(DISTINCT(COLLECT({Week Number}, {Rating}, = 1)))




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!