Formula to count distinct values in a column that contain a certain text string

Heidi D
Heidi D ✭✭
edited 09/20/22 in Formulas and Functions

I am looking for a way to count distinct values in a column, but only if the values contain a certain text string. For example, in my Fruit column, I'd like to count the unique values that contain "Large." In this case, the number should be 3.


Apple - Small

Apple - Medium

Apple - Large

Orange - Small

Orange - Medium

Orange - Medium

Orange - Large

Pear - Small

Pear - Medium

Pear - Large

Pear - Large

I've tried =COUNT(DISTINCT(Fruit:Fruit)), but that would give me all the distinct values, or 9.

I've tried =COUNTIF(Fruit:Fruit, CONTAINS("Large", @cell)), but that gives me all the instances of "Large," or 4.

Any ideas?



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!