Count unique values using criteria

Options

I am trying to use the count formula with distinct and collect. Here is the code:

=COUNT(DISTINCT(COLLECT([Job Filled Date:]:[Job Filled Date:], ISDATE(@cell), [Zone:]:[Zone:], ISTEXT(@cell))))

Here is what I am trying to do:


  • I want to count the row if the text in the Zone: column and the date in the Job Filled Date: column are unique.
  • In the above example, I want to return a value of 5. Even though there are 14 entries, there are only 5 unique values.
  • The above formula only returns a value of 1.

I am sure that I am missing something.

Thanks for the help in advance.

Best Answer

  • Maaik Meijerink
    Maaik Meijerink ✭✭✭✭✭
    Answer ✓
    Options

    Hello @Justin Mauzy,


    I added a new Column [Zone + Filled] with column formula, just to get the unique values based upon the two values together:

    =IF(AND(ISTEXT([Zone:]@row); ISDATE([Job Filled Date:]@row)); [Zone:]@row + "-" + [Job Filled Date:]@row)

    And then used the simple version of your formula getting the result of 5:

    =COUNT(DISTINCT([Zone + Filled]:[Zone + Filled]))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!