Examples of Count IF + Distinct

Ann Hannan

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

Hollie Green

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"))))

Ann Hannan

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

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"))))

Ann Hannan

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!