# 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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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!