Examples of Count IF + Distinct

Hi SmartSheet Community!

I have many instances where I want to know the number of distinct values based on specific criteria. I think I need a combination of COUNTIF and DISTINCT. Can anyone provide some guidance?

Here is what I have so far but its not working:

=COUNTIF(DISTINCT(TransactionRole:TransactionRole, HAS(@cell, "CDE")))

I'd love to know the proper order for the operations and parens, as well as when it is better to use CONTAINs, HAS, or equal to. The particular field I have indicated as the criteria for the IF is a multi-select drop-down field so I can not just rely on 'equal to' because there are entries which include CDE but also include other multi-select values.

Let me know if more description would be helpful!

Thanks,

Ann

Answers

  • Hollie Green
    Hollie Green Community Champion

    From my research you can't use Countif(Distinct together but you can use

    Count(Distinct(Collect Together which should serve the same function so for your formula above would be

    =COUNT(DISTINCT(COLLECT(Transactionrole:TransactionRole,Has(@cell,"CDE"))))

  • Thanks for your response, @Hollie Green. I wasn't sure how to use Collect.

    Something is still not correct because it is only returning the a value of "1". I tried it with something other than CDE as well and it was the same. Ideas?

    Ann

  • Hollie Green
    Hollie Green Community Champion

    oh wow sorry I didn't put in the range to collect just put in the criteria range

    =Count(Distinct(Collect(Transactionrole:TransactionRole,Transactionrole:TransactionRole,Has(@cell,"CDE"))))

  • Thanks, Hollie! That worked with a small tweak - the range to collect was the Entity Name. It returned a result of 26 which is in the expected range. Thank you for helping me understand the Distinct-Collect combo!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!